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
457 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Suggested Solutions

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

740 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