Converting CSV to Excel should be easy but....

[G_Waddell]

Hi Guys,

I'm obviously missing something here.

Basically I'm writing a program that will read in a csv file or excel file, and allows the user to pick which fields thay wish to use for what line of an address, and then import all the addresses to a SQL table for later processing.

I got the excel import working by opening it through ODBC as a database and reading it in and I thought that I'd save time by just converting the csv to excel and reusing the Excel routine on my new Excel file but I'm having difficulty with the csv conversion.

Here is what I'm using:

private sub ConvertCSV(OldName, NewName)
Try
                XL = New Excel.Application
                XL.Visible = False
                XL.Workbooks.OpenText(OldName)
                XL.DisplayAlerts = False
                XL.ActiveWorkbook.SaveAs(NewName, Excel.XlFileFormat.xlExcel9795, , , , False)
                XL.ActiveWorkbook.Close(False)
                XL.Quit()
                XL = Nothing

            Catch ex As Exception
                sError = ex.ToString ' Returns error details 
                MsgBox(sError, MsgBoxStyle.OkOnly)
                Debug.WriteLine(ex.ToString)
            End Try

end sub

The trouble is that when it then goes into my Excel Address routine, the new excel file appears to be empty.

I see an excel file outputted to where I want it on the computer but it will not open for me (no errors given excel just hangs)

Any ideas or will I have to parse the CSV file line by line into Excel?

[TomW]

Lots of different ways you can do the import but automatting Excel is probably the last one I would pick. First if you are importing directly from an Excel file, there is no reason to automate Excel at all. Using OLEDB you can read the values straight from the file into a dataset/datatable. This has an added advantage of not needing the user to also have the same version of Excel installed on the computer. Second there is no need to convert a CSV file before doing an import, there are many methods available for reading/importing delimited files. Not that it matters but you mention your using ODBC to read the Excel file, I dont see ODBC being used at all in your example.

I would suggest starting out by taking a look at the "TextFieldParser" in the help file. It has full examples for reading delimited files without the need to convert them to a different format.

[G_Waddell]

Lots of different ways you can do the import but automatting Excel is probably the last one I would pick. First if you are importing directly from an Excel file, there is no reason to automate Excel at all. Using OLEDB you can read the values straight from the file into a dataset/datatable. This has an added advantage of not needing the user to also have the same version of Excel installed on the computer. Second there is no need to convert a CSV file before doing an import, there are many methods available for reading/importing delimited files. Not that it matters but you mention your using ODBC to read the Excel file, I dont see ODBC being used at all in your example.

I would suggest starting out by taking a look at the "TextFieldParser" in the help file. It has full examples for reading delimited files without the need to convert them to a different format.

Yes the problem I was having was doing the convert from csv to Excel I had alreaduy got a routine working for importing the data from Excel file via Obdc - the csv /excel files are not coming in a standard format and I wanted the user to able to map the fields from Excel into the address table in SQL and also dedupe by running a select Distinct query.

Anyway, my code is working now which leads me to the following conclusions:
1. My previous attempts to work with Excel had left excel instances open on my machine or
2. I was looking at an old file from one of these attempts