Unable to open csv file format

vijayindukuri · Post by **vijayindukuri** » Wed Jun 28, 2006 2:10 am

Loveojha:
That won't convert it to csv, go to your xls editor, do a save as and save it as a csv file format.

You mean to say that every time we get excel input file we must do a save as in csv format. Can it be done in any other way any suggestions.

loveojha2 · Post by **loveojha2** » Wed Jun 28, 2006 2:39 am

You mean to say that every time we get excel input file we must do a save as in csv format. Can it be done in any other way any suggestions.

Yes exactly.

The other thing could be using the excel file through a DSN created of the workbook, using microsoft's drivers.

vijayindukuri · Post by **vijayindukuri** » Wed Jun 28, 2006 3:06 am

Thankyou all,
Who had spent their time and energy.
Any way that was a great learning.

chulett · Post by **chulett** » Wed Jun 28, 2006 6:22 am

vijayindukuri wrote:After renaming the file i tried to view the file by double cliking that,I was able to see that data in csv format.

Just for one last note - no, you didn't. Double-clicking on the renamed csv file simply launched Excel and you were looking at the spreadsheet. Again.

DSguru2B · Post by **DSguru2B** » Wed Jun 28, 2006 7:17 am

There are softwares present out-there that can do the conversion for you. Renaming the file doesnt change its structure internally. As your OS is windows, you can access the workbooks by creating an odbc connection.
Regards,

dsnovice · Post by **dsnovice** » Wed Jun 28, 2006 8:09 am

If the incoming excel file has only one sheet, no extra blank sheets and there are no special formatting. Then, you might be able to open it using sql file stage with delimiter as 009 (tab). Some times you would have to specify the file format as Dos if your ftp process doesnt take care of converting CRLF ---> Lf.

thank you,
a novice

rwierdsm · Post by **rwierdsm** » Wed Jun 28, 2006 8:37 am

Vijayindukuri,

That will not work. You have to open your xls in Excel and 'Save As' CSV (MS DOS). Renaming the file leaves it in Excel format, as you have seen, unreadable by a text editor.

Rob

vivek · Post by **vivek** » Wed Jun 28, 2006 8:56 am

Vijay indukuri,
I had the same problem while having source system as MS Access. What I did was i wrote an update statement to eliminate the newline carriage
UPDATE OPP_12JUNE SET BAMNOTES = replace(OPP_12JUNE.BAMNOTES,Chr$(10),'');
then i have exported to a location(say to the desktop for instance) then made it tab delimited and set other delimitres as none.
if am not wrong same should work for you. Reason is that even though you made the file tab delimited, it may still have some "Enter"(new line carriage) characters present. so first update the table the export as a tab delimited file.