Using schema.ini file, one should be able to define format of txt or csv file for import into Excel. I am using this functionality in some cases, so I am not absolute noobe here, perhaps first grader. However, I struggle to set up schema.ini file in one particular occasion. This is very important to me, as I have lots of txt;csv files coming out of PeopleSoft in this format:
"Instance","ID"
"1661437","NOVAT"
"1661432","NOVAT"
"1661439","NOVAT"
"1661445","NOVAT"
Please note that all fields are wrapped in double quotes and separated by comma which is also decimal separator on my PC. I therefore define in shema.ini file as follows:
[test.txt]
ColNameHeader=True
Format=Delimited(,)
MaxScanRows=5
CharacterSet=OEM
DecimalSymbol=.
Col1=Instance CHAR
Col2=ID CHAR
Decimal symbol is defined, not for this particular example, just in other cases where I have numbers, dot is actually decimal separator.
I then try the usual Data Import path:
Data->Import External Data->New Database Query -> <New Data Source>
I then give any name to data source, select txt, csv driver and click Connect. On the following screen I select directory and click Options. I then get first error message :
<table style="width:auto;"><tr><td><a href="http://picasaweb.google.com/lh/photo/gEFePdr5aiIqkgYks9vbyA?feat=embedwebsite"><img src="http://lh6.ggpht.com/_7WhOMvuTydM/SgmjAwG1IhI/AAAAAAAABIg/-b9mRgZ05pU/s144/Error1.JPG" /></a></td></tr><tr><td style="font-family:arial,sans-serif; font-size:11px; text-align:right">From <a href="http://picasaweb.google.com/ajocius/Temp_Excel?feat=embedwebsite">Temp_Excel</a></td></tr></table>
I then try to disregard error message and select the file I want to import and make Excel guess the structure. This steps works on simple and straight forward examples. Not on mine one howewer. I get second error message:
<table style="width:auto;"><tr><td><a href="http://picasaweb.google.com/lh/photo/mAfhtEM3J-bDF3sb2tYVbQ?feat=embedwebsite"><img src="http://lh4.ggpht.com/_7WhOMvuTydM/SgmjAyo3drI/AAAAAAAABIk/jaRM3jOWj8E/s144/Error2.JPG" /></a></td></tr><tr><td style="font-family:arial,sans-serif; font-size:11px; text-align:right">From <a href="http://picasaweb.google.com/ajocius/Temp_Excel?feat=embedwebsite">Temp_Excel</a></td></tr></table>
It looks like Excel does not like that fields are separated by comma. But this is unfortunatelly how Peoplesoft exports data and I can not change it. I thought this was the idea of schema.ini file to actually tell Excel how fields are separated, which would overrule default regional settings in control panel.
I managed to get arround be opening txt file and replacing all commas with | and then defining it as delimiter. That works, but kind of strange workaround and also additional step for every file exported...
How can I import data without manual changes I do now to txt file. I just want standad Peoplesoft file with shema.ini as a companion. Is that too much to ask ?
"Instance","ID"
"1661437","NOVAT"
"1661432","NOVAT"
"1661439","NOVAT"
"1661445","NOVAT"
Please note that all fields are wrapped in double quotes and separated by comma which is also decimal separator on my PC. I therefore define in shema.ini file as follows:
[test.txt]
ColNameHeader=True
Format=Delimited(,)
MaxScanRows=5
CharacterSet=OEM
DecimalSymbol=.
Col1=Instance CHAR
Col2=ID CHAR
Decimal symbol is defined, not for this particular example, just in other cases where I have numbers, dot is actually decimal separator.
I then try the usual Data Import path:
Data->Import External Data->New Database Query -> <New Data Source>
I then give any name to data source, select txt, csv driver and click Connect. On the following screen I select directory and click Options. I then get first error message :
<table style="width:auto;"><tr><td><a href="http://picasaweb.google.com/lh/photo/gEFePdr5aiIqkgYks9vbyA?feat=embedwebsite"><img src="http://lh6.ggpht.com/_7WhOMvuTydM/SgmjAwG1IhI/AAAAAAAABIg/-b9mRgZ05pU/s144/Error1.JPG" /></a></td></tr><tr><td style="font-family:arial,sans-serif; font-size:11px; text-align:right">From <a href="http://picasaweb.google.com/ajocius/Temp_Excel?feat=embedwebsite">Temp_Excel</a></td></tr></table>
I then try to disregard error message and select the file I want to import and make Excel guess the structure. This steps works on simple and straight forward examples. Not on mine one howewer. I get second error message:
<table style="width:auto;"><tr><td><a href="http://picasaweb.google.com/lh/photo/mAfhtEM3J-bDF3sb2tYVbQ?feat=embedwebsite"><img src="http://lh4.ggpht.com/_7WhOMvuTydM/SgmjAyo3drI/AAAAAAAABIk/jaRM3jOWj8E/s144/Error2.JPG" /></a></td></tr><tr><td style="font-family:arial,sans-serif; font-size:11px; text-align:right">From <a href="http://picasaweb.google.com/ajocius/Temp_Excel?feat=embedwebsite">Temp_Excel</a></td></tr></table>
It looks like Excel does not like that fields are separated by comma. But this is unfortunatelly how Peoplesoft exports data and I can not change it. I thought this was the idea of schema.ini file to actually tell Excel how fields are separated, which would overrule default regional settings in control panel.
I managed to get arround be opening txt file and replacing all commas with | and then defining it as delimiter. That works, but kind of strange workaround and also additional step for every file exported...
How can I import data without manual changes I do now to txt file. I just want standad Peoplesoft file with shema.ini as a companion. Is that too much to ask ?
