Insert csv rows to database

Help
, ,
1

Hi All,

I want to insert all CSV file rows into SQL database. I have exact same fields in both csv and database table. I setup database connection successful. I am struggling to process insert statement.

I am using for each activity to read each row and my SQL database name is “project” and table name is “dbo.dump”.

Can someone suggest what should be my query? specifically, I am not able to understand what should I write in Values in insert statement because I cannot use row.Item(0).ToString as value.

Please help and Thanks in advance.

Thanks
Shyamal Dave

2

@shyamaldave, you can use Insert activity and use datatable directly.

Reference: How to insert data dynamically in data table - #5 by Jasnoor

Regards,
Dominic :slight_smile:

3

Hi Shyamal Dave,

CSV to SQL Database table workflow:

  1. Drop sequence activity.
  2. Drop Connect activity inside the sequence, Create a variable for Database Connection ,select the Provider as SqlClient, string in the property window.
  3. Read the CSV file using Read CSV activity. Create a variable for output Data Table in the property window.
  4. Create a variable of type Data Table and assign the value from previous step.
    5.Drop For Each Row activity. give Table variable name from previous step, Loop through each row
  5. Drop Execute Non Query activity, give connection string name created in step 2,select the provider as SqlClient,
  6. Sql insert query
    “INSERT Into SqlDataTable (Column1,Column2,Column3)
    values (@value1,@value2,@value3)”
    8.Parameters should have the same datatype as Sql Data Table.
  7. assign the value as row.Item(0).ToString,
    image

All the best.

4

I have a text file like this:

image

and will insert it into a table (database). How?
Thanks

5

Hi @prathapr

I’m currently using your suggestion but don’t understand the "Loop through each row"part. Could you explain further?

Also does this work the same for Microsoft Access?

Thanks

Ryan