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
451 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
ID: 39707802
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
ID: 39725598
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

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
string fuctions 4 25
Need a SQL query that creates a header row and one or more detail rows. 7 30
T-SQL: Do I need CLUSTERED here? 13 41
Help in Bulk Insert 9 30
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.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties

785 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