Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 488
  • Last Modified:

How to feed a SQL database from a CSV file??How to import a CSV file to a SQL database with VBA??

Hello Experts

I want to create a script to import a csv file to a SQL database. The CSV file is located in my pc and the database in a server.
The idea is to execute the script and this should open the csv file and import it to MSSQL. Is this possible? How can I do it?

Kind regards
0
acunaara
Asked:
acunaara
1 Solution
 
SAMIR BHOGAYTAFreelancer and IT ConsultantCommented:
hi.. try this....

/*
 * Loads the csv file into a DataSet.
 *
 * If the numberOfRows parameter is -1, it loads all rows, otherwise it
 * loads the first specified number of rows (for preview)
 */

public DataSet LoadCSV(int numberOfRows)
{
    DataSet ds = new DataSet();
    try
    {
        // Creates and opens an ODBC connection
        string strConnString = "Driver={Microsoft Text Driver (*.txt; *.csv)};
            Dbq=" + this.dirCSV.Trim() + ";
            Extensions=asc,csv,tab,txt;Persist Security Info=False";
        string sql_select;
        OdbcConnection conn;
        conn = new OdbcConnection(strConnString.Trim());
        conn.Open();

        //Creates the select command text
        if (numberOfRows == -1)
        {
            sql_select = "select * from [" +
                    this.FileNevCSV.Trim() + "]";
        }
        else
        {
            sql_select = "select top " + numberOfRows +
                " * from [" + this.FileNevCSV.Trim() + "]";
        }

        //Creates the data adapter
        OdbcDataAdapter obj_oledb_da = new OdbcDataAdapter(sql_select, conn);

        //Fills dataset with the records from CSV file
        obj_oledb_da.Fill(ds, "csv");

        //closes the connection
        conn.Close();
    }
    catch (Exception e) //Error
    {
        MessageBox.Show(e.Message, "Error - LoadCSV",
                MessageBoxButtons.OK,MessageBoxIcon.Error);
    }
    return ds;
}

or

Based SQL Server CSV Import

1) The CSV file data may have "," (comma) in between (Ex: description), so how can I make import handling these data?

Solution

If you're using "," (comma) as delimiter than there is no way to differentiate between comma in field terminator and comma in your data. I would use a different FIELDTERMINATOR like ||.

So code would look like and this will handle comma and single slash perfectly.

2) If the client create the csv from excel then the data that have comma are enclosed within "" (double quotes) [as the below example] so how do the import can handle this?

Solution

If you're using BULK insert then there is no way to handle double quotes, data will be inserted with double quotes into rows. after inserting the data into table you could replace those double quotes with ''.

update table
set columnhavingdoublequotes = replace(columnhavingdoublequotes,'"','')

3) How do we track if some rows have bad data, which import skips? (does import skips rows that are not importable)?

Solution

To handle rows which aren't loaded into table because of invalid data or format, could be handle using ERRORFILE property, specify the error file name, it will write the rows having error to error file. code should look like.

BULK INSERT SchoolsTemp
    FROM 'C:\CSVData\Schools.csv'
    WITH
    (
    FIRSTROW = 2,
    FIELDTERMINATOR = ',',  --CSV field delimiter
    ROWTERMINATOR = '\n',   --Use to shift the control to next row
    ERRORFILE = 'C:\CSVDATA\SchoolsErrorRows.csv',
    TABLOCK
    )
0
 
acunaaraAuthor Commented:
The only thing is how can i set the Date/Time fields to DD/MM/YYYY before import it to SQL database?
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now