Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
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
Medium Priority
?
476 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 1500 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

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

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

For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Windocks is an independent port of Docker's open source to Windows.   This article introduces the use of SQL Server in containers, with integrated support of SQL Server database cloning.
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 insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

688 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