FSO and CSV file Can it be done

[shawn876]

Hi:
I have a CSV file which I need to import into a SQL Server table. But I created a form on an asp page. I needed to know if I can use FSO to import this csv file into the sql server table. If yes Does anyone have a smaple of this code.
Thanks

 

[mrmovie]

from what i can gather a CSV file is comma seperated,
so you should read you file line by line
and for each line you should Split() it based on a ","
this will return an array of your values which you can write to your SQL db.
all that being said i am sure ADO has a way of reading CSV files and returning a recordset which you could then write back to your SQL DB, am i going in circles??

 

[dilettante]

What about combining the information from:

thread329-781818

With the information from:

HOW TO: Import Data into SQL Server from Excel

The idea is to create an ADO connection using the Jet OLEDB Provider. Have this Provider use the Jet Text Driver (instead of the Excel driver) to read the CSV file, and push the imported text to the SQL Server database via a connection using a SQL statement something like:

    cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
        "Data Source='<csvpath>';" & _
        "Extended Properties=Text"
    
    'Import by using Jet Provider.
    strSQL = "SELECT * INTO [odbc;Driver={SQL Server};" & _
        "Server=<server>;Database=<database>;" & _
        "UID=<user>;PWD=<password>].<newtable> " & _
        "FROM <filetoimport.csv>"
    cn.Execute strSQL, lngRecsAff, adExecuteNoRecords
    MsgBox "Records affected: " & lngRecsAff

Of course this is a hack, you need to look it over.

Replace things like <csvpath> and <server> with the actual path, etc. You also need to create a schema.ini file in the CSV file's directory to tell the Jet Text Driver what sort of delimiters your text file has, the field names to use, etc.