Solved

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

Posted on 2013-12-09
2
445 Views
Last Modified: 2013-12-17
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
Comment
Question by:acunaara
2 Comments
 
LVL 11

Accepted Solution

by:
SAMIR BHOGAYTA earned 500 total points
Comment Utility
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
 

Author Closing Comment

by:acunaara
Comment Utility
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

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Complex SQL 10 32
Numeric sequence in SQL 14 36
SQL JOIN 6 27
Protecting vb6 & .Net code Obfuscation 18 43
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Viewers will learn how the fundamental information of how to create a table.

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now