excel and sql

[gjoisa]

hello all,
is it possible to copy data from MS Excel file to a sql database ? If yes then how to write code ?:sign0085:

 

[Erel]

Open the Excel file and save it as a CSV file.
Load the CSV file to a Table control with Table.LoadCSV.
Use Connection.CreateSQLTable to create an SQL table from the Table control.

 

[gjoisa]

Dear Erel,
I have tried according to your direction . But while running the app, an error message occurs as it can not find the perticular csv file , though the perticular file situated in the same directory . What to do ?

 

[agraham]

Are you referring to it as "Table.LoadCSV(AppPath & "\file.csv")"?

 

[gjoisa]

I have attached my file here . But i could not enclose my csv file here.

 

[agraham]

The problem is not reading the csv but that you are confusing the actual database with a table within the database. You need to create or open that database before you create a table in it.

con.New1
table.LoadCSV(AppPath&"\tempad.csv",",",true,true)
con.Open("Data Source = " & AppPath & "\AdiAtlas.sl3")
con.CreateSQLTable("table","CSVimport")
con.Close

 

[gjoisa]

Dear Agraham , I am very thankful to you for your timely response . But the same error persists . The error message reads so " An error occured on sub app_start . Line number: 10 . table.LoadCSV(AppPath & "\tempad.csv",",",true,true) Error description : Could not find file 'F:\A\Basic4PPC\tempad.csv '. Continue ? " . Is there any problem with my 'tempad.csv' ? I just saved an excel file as tempad.csv . However I attached all files here . Can u just check these files once and suggest me the right things .:sign0085:

 

[agraham]

I just saved an excel file as tempad.csv

I'm afraid you didn't, you saved it as an XLS file named tempad.csv.xls! Just naming it as a csv doesn't work. You need to do a File-> Save As and in the drop down list box next to "Save as type" select "CSV(MS-DOS) (*.csv)". Your version of Excel may differ slightly but the option will still be there.

 

[gjoisa]

Thank you agraham . It is working! I didnot know how to convert an MS Excel file to csv . You helped me very much . Thank you again .