Select specific column in CSV and insert into SQL table

Question

1.50/5 (2 votes)

See more:

Hi everyone,

I have a CSV file (TAB delimited) with lot of columns and rows.

For example:

A B C D E F G H I L M N O
1 2 3 4 5 6 7 8 9 0 1 2 3

I wanto to select only specific columns, like C H L.

After obtaining the results from the columns C H L , I wanto to put data into a SQL table using INSERT statment.

How can i do it ?

Best Regards

Posted 27-Oct-15 22:35pm

Member 10558090

Add a Solution

2 solutions

Solution 1

Write a CSV parser or get one from the web (see for instance this Code Project article CSV Parser (C#)[^]), grab the requested data and then insert them into the database (you may find man y tutorials available just Googling for[^]).

Posted 27-Oct-15 22:47pm

CPallini

Comments

Member 12597808 22-Jun-16 7:19am

how to select particular colums from csv file and then then insert into sql table using asp.net code and using stored procedure

Member 12597808 22-Jun-16 7:21am

please mail me code

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Richard MacCutchan · Accepted Answer · 2015-10-27T22:48:00

Solution 2

You can import the CSV into a dataset as described in Using OleDb to Import Text Files (tab, CSV, custom)[^]. This will allow you to extract specific columns quite easily.

Posted 27-Oct-15 22:48pm

Richard MacCutchan

Comments

Member 10558090 28-Oct-15 5:16am

Hi, can i do something like this ?
connString = "My connection string";
SqlConnection connect = new SqlConnection(connString)

string query = "INSERT INTO ecList (SKU,Description,Price) VALUES (@sku,@description,@price);

using (StreamReader file = new StreamReader(nomefile))
{
string line;

while ((line = file.ReadLine()) != null)
{
if (riga.Trim().Length > 0)
{
string[] columns = line.Split(Convert.ToChar(delimiter));

cmd.Parameters.AddWithValue("@sku", columns[2].ToString();
cmd.Parameters.AddWithValue("@description", columns[4].ToString();
cmd.Parameters.AddWithValue("@price", columns[9].ToString();
cmd.ExecuteNonQuery();
}
}
}

Richard MacCutchan 28-Oct-15 5:56am

Probably; why not try it?

Member 10558090 28-Oct-15 7:16am

Yes, but I have this error:

Error converting data type nvarchar to numeric.

SqlCommand value and columns in my SQL Table are the same.

int idfornitore = 6;

string connString = "Data Source=192.168.50.18,1433; Network Library=DBMSSOCN; Initial Catalog = Gestionale; User ID=sa; Password=netinfo;";

SqlConnection connessione = new SqlConnection(connString);

string inserimento = "INSERT INTO dbo.ecListinoFornitori (IDFornitore,CodiceArticolo,CodiceArticoloFornitore,Descrizione,Giacenza,GiacenzaData,Prezzo,PrezzoVendita) VALUES (@idfornitore,@codicearticolo,@codicearticolofornitore,@descrizione,@giacenza,@giacenzadata,@prezzo,@prezzovendita)";

SqlCommand cmd = new SqlCommand(inserimento, connessione);

connessione.Open();

using (TextFieldParser reader = new TextFieldParser(txtPercorsoNEXT.Text))
{
reader.SetDelimiters(new string[] { txtSeparatoreNEXT.Text.ToString() });
reader.HasFieldsEnclosedInQuotes = true;

reader.ReadLine();

string oggi = DateTime.Now.ToString("dd-MM-yyyy");

while (!reader.EndOfData)
{
string[] campi = reader.ReadFields();

cmd.Parameters.Add("@idfornitore", SqlDbType.Int).Value = idfornitore;

cmd.Parameters.Add("@codicearticolo", SqlDbType.VarChar).Value = campi[0].ToString();

cmd.Parameters.Add("@codicearticolofornitore", SqlDbType.VarChar).Value = campi[1].ToString();

cmd.Parameters.Add("@descrizione", SqlDbType.Text).Value = campi[4].ToString();

if (campi[5].Contains("N"))
{
int NoStock = 0;

cmd.Parameters.Add("@giacenza", SqlDbType.Int).Value = NoStock;
}
else if (campi[5].Contains("S"))
{
int inStock = 1;

cmd.Parameters.AddWithValue("@giacenza", SqlDbType.Int).Value = inStock;
}

cmd.Parameters.AddWithValue("@giacenzaData", SqlDbType.VarChar).Value = oggi.ToString();

cmd.Parameters.AddWithValue("@prezzo", SqlDbType.Decimal).Value = campi[9].ToString();

cmd.Parameters.AddWithValue("@prezzovendita", SqlDbType.Decimal).Value = campi[9].ToString();

cmd.ExecuteNonQuery();
}

Richard MacCutchan 28-Oct-15 9:57am

Why are you using ToString on all your fields? Most of them are already strings, so that call is redundant. Also if a database field is not a string type then you should not be passing a String as the parameter. And finally, do not store dates in string form, store them as Date or DateTime types.

Member 10558090 28-Oct-15 12:52pm

Ok, I solved all my problems.
Thank you.