Unable to import .csv file in to MSSQL table

Hi,

I have a csv file I need to import in to a MSSQL table but the last three records of the attached .csv file don't come in, well, all the rest of the data comes in to the database just not the UPC Code, these codes on the last three records have an 'a' at the end but I don't know why this would be an issue.

Any help on this would be much appreciated.

Regards,

Ken

Code.txt 966-02-006-NSL-AN53-AN53-S.CSV
kenuk110Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

ambidextrousCommented:
Ken -

  I'm not exactly sure how to test the code, but it seems to me that the provider is 'automatically' casting the first column as an "Integer" rather than a string.  There are a lot of possible tweaks.

  My suggestion (without actually being able to test-run the code) would be to play with your "SELECT * from [xxxxx]" statement.  See if you can define each individual column and CAST/Convert them as string.  "SELECT Cast([UPC Code] as String), ...."

Hope this helps.
0
Todd GerbertIT ConsultantCommented:
You can manually read & parse the CSV file, or possibly use a schema.ini to force columns to particular data types (http://msdn.microsoft.com/en-us/library/ms709353(v=vs.85).aspx).
0
kenuk110Author Commented:
Hi,

All I get if I position the CAST bit is this error: ErrorInfo.GetDescription failed with E_FAIL(0x80004005

Ive tried it all these ways:

string CmdText = "select Cast([UPC Code] as String) *  from  [" + PG.PPathCSV + "] ";

string CmdText = "select  * Cast([UPC Code] as String) from  [" + PG.PPathCSV + "] ";

string CmdText = "select  *  from  [" + PG.PPathCSV + "] Cast([UPC Code] as String)";

All of them give me the same error. I also looked at the read/parse thing but it's way over my head, I'm trying to get someone else's code working here and I'm no programmer.

Any other suggestions??

Sorry to be stupid.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

kenuk110Author Commented:
I just noticed that this line in the code is trying to change the UPC Code to a string but in the file the heading is UPC Code and in the code its UPCCode without a space. Would this be the issue and if so, how do I tell it to use the correct format of UPC Code, I tried wrapping it in [ ] and in ' ' and in " " but I just get red lines underneath.

0
kenuk110Author Commented:
Ooops, forgot to enter the line of code:


pcode.UPCCode = Ds.Tables[0].Rows[i][0].ToString();

Open in new window

0
kenuk110Author Commented:
Just for more info, the table the value is going in to, all columns are set to NVARCHAR(255) and I can type anything I want 'manually' into this field in the DB.

0
Anthony PerkinsCommented:
What is happening is that SQL Server is guessing what type of file it is, from the first few rows and guessing wrong.  It is your responsibility to force it to use character data as opposed to numeric.
0
kenuk110Author Commented:
Hi,

I know you're right, the problem I have is that I don't have the technical knowledge to figure out how to do that, any assistance would be really appreciated. I'm not a programmer or a DB person to be honest but I'm trying!!

Any pointer or help would be much appreciated.

Ken
0
cubaman_24Commented:
Hello:
Try converting your csv to excell. Excell has strongly typed columns, and is less problematic than text files about text encoding and things like that.
Best regards.
0
kenuk110Author Commented:
Hi,

These files come from another software package and the guys that create them just create them then upload them. I can't use Excel although I completely agree with you. Is there really no way to get all these in a strings?? I'm at a loss now.
0
Todd GerbertIT ConsultantCommented:
Yes, you can manually parse the file yourself: open the file and read it a line at a time, split each line on the comma character.

List<string[]> rows = new List<string[]>();
using (StreamReader file = new StreamReader("C:\\file.csv"))
{
	while (!file.EndOfStream)
	{
		rows.Add(file.ReadLine().Split(','));
	}
}

foreach (string[] row in rows)
{
	Console.Write(row[0]);
	Console.Write(row[1]);
	Console.Write(row[2]);
}

Open in new window


You can also use a schema.ini file to force the datatype of the columns in the text file: http://msdn.microsoft.com/en-us/library/ms709353(v=vs.85).aspx
0
ambidextrousCommented:
Ken -

  Here are two other attempts to try...  I'm shooting in the dark a bit, but maybe these will help trigger some other good ideas.

Option #1 - Casting individual Columns
string CmdText = "
                  SELECT
                    Cast([UPC Code] as String)
                   ,Cast([ShelfNO] as String)
                   ,Cast([KeyNo] as String)
                   ,Cast([Item Code] as String)
                   ,Cast([Item Name] as String)
                   ,Cast([Description] as String)
                   ,Cast([Side] as String)
                   ,Cast([Stack] as String)
                   ,Cast([Hook] as String)
                   ,Cast([Layer Nbr] as String)
                   ,Cast([FaceNo] as String)
                   ,Cast([Nbr Faces] as String)
                  FROM [" + PG.PPathCSV + "]
                 ";

Open in new window


Option 2 - Attempt a UNION to "Force Data Type"  Ignore the "XXXXX" row out during loop.
string CmdText = "SELECT 'XXXXXXXXXXXXXXXXXXXXXXXXXXXX' as [UPC Code]
                        ,'XXXXXXXXXXXXXXXXXXXXXXXXXXXX' as [ShelfNO]
                        ,'XXXXXXXXXXXXXXXXXXXXXXXXXXXX' as [KeyNo]
                        ,'XXXXXXXXXXXXXXXXXXXXXXXXXXXX' as [Item Code]
                        ,'XXXXXXXXXXXXXXXXXXXXXXXXXXXX' as [Item Name]
                        ,'XXXXXXXXXXXXXXXXXXXXXXXXXXXX' as [Description]
                        ,'XXXXXXXXXXXXXXXXXXXXXXXXXXXX' as [Side]
                        ,'XXXXXXXXXXXXXXXXXXXXXXXXXXXX' as [Stack]
                        ,'XXXXXXXXXXXXXXXXXXXXXXXXXXXX' as [Hook]
                        ,'XXXXXXXXXXXXXXXXXXXXXXXXXXXX' as [Layer Nbr]
                        ,'XXXXXXXXXXXXXXXXXXXXXXXXXXXX' as [FaceNo]
                        ,'XXXXXXXXXXXXXXXXXXXXXXXXXXXX' as [Nbr Faces]
                  UNION SELECT
                    Cast([UPC Code] as String)
                   ,Cast([ShelfNO] as String)
                   ,Cast([KeyNo] as String)
                   ,Cast([Item Code] as String)
                   ,Cast([Item Name] as String)
                   ,Cast([Description] as String)
                   ,Cast([Side] as String)
                   ,Cast([Stack] as String)
                   ,Cast([Hook] as String)
                   ,Cast([Layer Nbr] as String)
                   ,Cast([FaceNo] as String)
                   ,Cast([Nbr Faces] as String)
                  FROM [" + PG.PPathCSV + "] 
                 ";

Open in new window


Good luck!
0
cubaman_24Commented:
Hello again:
In the sql import/export wizard, after selecting the file to import, you can click in advanced properties in the rigth side and select the data type of each column to import. Just select the right type (ie  decimal DT_DECIMAL, eight byte signed integer DT_I8) etc.

Best regards.
0
kenuk110Author Commented:
Hi,

Thanks for all your responses, I'm trying yours 'ambidextrous' but I now get the following error thrown:

IErrorInfo.GetDescription failed with E_FAIL(0x80004005)

I have tried both your suggestions and I get this on both.

To all others that have suggested, I REALLY appreciate it, I simply don't know how to implement what you are suggesting, I'm not a programmer or DB guy, I'm trying to get someone elses code working who left the company so I'm in fire fighting mode and losing!

Any suggestions to the error and drip feeding me with steps would be greatly appreciated.

Cheers,

Ken
0
Todd GerbertIT ConsultantCommented:
Have you had a look at http://msdn.microsoft.com/en-us/library/ms709353(v=vs.85).aspx yet?

Probably all you need is a text file named schema.ini in the same folder as the CSV you're importing with contents like:
[966-02-006-NSL-AN53-AN53-S.CSV]
Format=CSVDelimited
ColNameHeader=True
MaxScanRows=0

Open in new window


And the code you originally had.
0
ambidextrousCommented:
Ken -

  Here's another one that I thought of...  try to remove the option that mentions the header row.

string ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + MyPath + ";Extended Properties='text;HDR=No;FMT=Delimited'";

This should make the Jet ODBC connection recognize the first row as all string values.  Then, during the looping piece, have it ignore where the the first column = "UPC Code".

Does that makes sense?
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
kenuk110Author Commented:
Hi,

It does yes, I'll change the connection string, do I use the CAST suggestion or the one with loads of XXX in it? Also, do I leave the XXX's in or do I have to take them out?
0
ambidextrousCommented:
I would leave the SQL string as you originally sent it:

string CmdText = "SELECT * FROM [" + PG.PPathCSV + "] ";
0
Todd GerbertIT ConsultantCommented:
Try this, it's a little less "hacky" and a little more "proper" than just skipping the first row.  The schema.ini file tells the Jet database provider how to handle text files.  MaxScanRows tells it to scan the entire file to determine column types (so it will reach the UPC's with a trailing "a" and determine that column is text).  You could further customize it by explicitly defining the data type of each column in the text file, refer to http://msdn.microsoft.com/en-us/library/ms709353(v=vs.85).aspx for additional details.

using (DBPlanogram PG = new DBPlanogram(long.Parse(hcb.Value.ToString())))
                    {
                        PG.Pconfirm = "1";
                        PG.Update();

                        if (!string.IsNullOrEmpty(PG.PPathCSV))
                        {
                            try
                            {
                                
                                ///// update Planogram Code Table 
                                string MyPath = System.IO.Path.GetDirectoryName(Server.MapPath("./Uploadfiles/" + PG.PPathCSV)); //Server.MapPath("~/Uploadfiles/" + PG.PPathCSV);
                                string ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + MyPath + ";Extended Properties='text;HDR=Yes;FMT=Delimited'";

								// Write a schema.ini file
								System.IO.File.WriteAllText(System.IO.Path.GetDirectoryName(MyPath) + "\\schema.ini", "[" + System.IO.Path.GetFileName(PG.PPathCSV) +"]\r\nFormat=CSVDelimited\r\nColNameHeader=True\r\nMaxScanRows=0");

								// Remainder of code unchanged until deletion of schema.ini

                                string CmdText = "select * from  [" + PG.PPathCSV + "]";

                                //string CmdText = "select * from  " + PG.PPathCSV;

                                OleDbConnection Con = new OleDbConnection(ConnectionString);
                                OleDbDataAdapter adptr = new OleDbDataAdapter(CmdText, Con);
                                //OleDbCommand Cmd = new OleDbCommand(CmdText + txtFileName.Text, Con);   
                                //DataSet ds1 = new Planogram.Datasets.PlanogramCodeDS();
                                DataSet Ds = new DataSet();
                                adptr.Fill(Ds);

                                if (Ds.Tables[0].Rows.Count > 0)
                                {
                                    for (int i = 0; i < Ds.Tables[0].Rows.Count; i++)
                                    {
                                        using (DBPlanogramCode pcode = new DBPlanogramCode())
                                        {
                                            pcode.PID = PG.PID;
                                            pcode.PVendor = PG.PVendor;
                                            pcode.Country = PG.Country;
                                            pcode.PLocation = PG.PLocation;
                                            pcode.Store = PG.Store;
                                            pcode.BlockNo = PG.BlockNo;
                                            pcode.UPCCode = Ds.Tables[0].Rows[i][0].ToString();
                                            pcode.ShelfNo = Ds.Tables[0].Rows[i][1].ToString();
                                            pcode.KeyNo = Ds.Tables[0].Rows[i][2].ToString();
                                            pcode.ItemCode = Ds.Tables[0].Rows[i][3].ToString();
                                            pcode.ItemName = Ds.Tables[0].Rows[i][4].ToString();
                                            pcode.Description = Ds.Tables[0].Rows[i][5].ToString();
                                            pcode.Side = Ds.Tables[0].Rows[i][6].ToString();
                                            pcode.Stack = Ds.Tables[0].Rows[i][7].ToString();
                                            pcode.Hook = Ds.Tables[0].Rows[i][8].ToString();
                                            pcode.LayerNbr = Ds.Tables[0].Rows[i][9].ToString();
                                            pcode.FaceNo = Ds.Tables[0].Rows[i][10].ToString();
                                            pcode.NbrFaces = Ds.Tables[0].Rows[i][11].ToString();
                                            //pcode.StoreType = PG.PPathCSV.Substring(20, 1).ToUpper();
                                            pcode.StoreType = PG.StoreType;
                                            pcode.Insert();
                                        }
                                    }
                                }
								// Delete the schema.ini file
								System.IO.File.Delete(System.IO.Path.GetDirectoryName(MyPath) + "\\schema.ini");
                            }

		}

Open in new window

0
Todd GerbertIT ConsultantCommented:
Or, assuming a comma will never appear as in the data, it's easy to split the contents of the file yourself:
using System;
using System.Collections.Generic;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
using System.Data.OleDb;
using System.IO;

public partial class _Default : System.Web.UI.Page
{
	protected void RunImportButton_Click(object sender, EventArgs e)
	{
		using (DBPlanogram PG = new DBPlanogram(long.Parse(hcb.Value.ToString())))
		{
			PG.Pconfirm = "1";
			PG.Update();

			if (!string.IsNullOrEmpty(PG.PPathCSV))
			{
				try
				{

					///// update Planogram Code Table 
					string MyPath = System.IO.Path.GetDirectoryName(Server.MapPath("./Uploadfiles/" + PG.PPathCSV)); //Server.MapPath("~/Uploadfiles/" + PG.PPathCSV);

					List<string[]> rows = ParseCsv(MyPath, true);

					for (int i = 0; i < rows.Count; i++)
					{
						using (DBPlanogramCode pcode = new DBPlanogramCode())
						{
							pcode.PID = PG.PID;
							pcode.PVendor = PG.PVendor;
							pcode.Country = PG.Country;
							pcode.PLocation = PG.PLocation;
							pcode.Store = PG.Store;
							pcode.BlockNo = PG.BlockNo;
							pcode.UPCCode = rows[i][0];
							pcode.ShelfNo = rows[i][1];
							pcode.KeyNo = rows[i][2];
							pcode.ItemCode = rows[i][3];
							pcode.ItemName = rows[i][4];
							pcode.Description = rows[i][5];
							pcode.Side = rows[i][6];
							pcode.Stack = rows[i][7];
							pcode.Hook = rows[i][8];
							pcode.LayerNbr = rows[i][9];
							pcode.FaceNo = rows[i][10];
							pcode.NbrFaces = rows[i][11];
							//pcode.StoreType = PG.PPathCSV.Substring(20, 1).ToUpper();
							pcode.StoreType = PG.StoreType;
							pcode.Insert();
						}
					}
				}
				catch (Exception ex)
				{
				}
			}
		}
	}

	private List<string[]> ParseCsv(string PathToCsv, bool SkipFirstRow)
	{
		StreamReader csvStream = new StreamReader(PathToCsv);

		if (SkipFirstRow)
			csvStream.ReadLine();

		List<string[]> rows = new List<string[]>();

		while (!csvStream.EndOfStream)
		{
			// Read one line from file
			string line = csvStream.ReadLine();

			// Split into a string array on the comma
			string[] linePieces = line.Split(',');

			// Add the string array to the list of rows
			rows.Add(linePieces);
		}

		csvStream.Dispose();

		return rows;
	}
}

Open in new window

0
kenuk110Author Commented:
ambidextrous,

I just tried what you said, brilliant!!! It works!!!! It does bring in the header text but thats no issue, I delete it from the table prior to doing anything with the data so it's perfect. Thank you so much for assisting me with this, really appreciate it.  Everyone else, I really appreciate all your suggestions and I'm sure they would have worked if I had the knowledge to implement them so sorry but a huge thank you for helping.

Cheers,

Ken
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
C#

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.