April 1, 2003 at 3:23 pm
I try to load all rows in an Excel file to a dummy data table. But the data in the row 4 and 5 (they are string values) are missing. Anyone knows why? or tell me the way to pin-point this problem?
Thanks.
April 2, 2003 at 12:49 pm
April 2, 2003 at 12:54 pm
Not really. My problem is that not all data in a row are missing. I found that there are more than half of the data is missing, but another half of data is interpreted ok. So not sure what is the way to gurantee all the fields are transformed correctly.
Is there any way to attached files in this forum while discussig problem? I am thinking to attach my Excel file so that people can look at it and understand the problem better.
Thank you.
April 2, 2003 at 1:08 pm
Is all data in one sheet?
Maybe you have extra comma in the file is causing the problem
Johnny...
April 2, 2003 at 1:26 pm
Yes, all the data are in one sheet.
In fact, some of the data which is missing are text with "," in between the text. In this case, what is the solution? I don't want to apply double quote for all the text fields I have. That will be too much of them. Without applying double quote to all the text fields, I can't choose the option of using "double quote" as "text qualifier".
However, there are some data which is missing does not have comma in between the text. For those, I really don't know what is wrong.
April 3, 2003 at 7:17 am
Hi,
One solution: Create a .csv file from excel before import this file to sql clean all the extra commas using regular expressions and then import the file.
Example for regular expressions
http://www.4guysfromrolla.com/webtech/regularexpressions.shtml
I hope this help.
Johnny...
April 8, 2003 at 12:03 pm
I had this problem before using excel and loading into a table with some rows missing. The best way to do this is to change it to a tilde delimited text file and do the import.
Jules Bui
IT Operations DBA
Backup and Restore Administrator
April 8, 2003 at 12:40 pm
buijules:
How do you change the file in a tilde delimite file? Excel does not have the option to save files into this format.
April 10, 2003 at 10:52 am
First try to upload the data to the SQL table.Try this, create a DTS which creates the new table and extract the data from excel to SQL table and validate the data and try to match or map the New table with the dummy(Old one).Chk the no. of columns & type and try to tranfer the data b/w these table and It gives you the exact problem.
Edited by - spdhiva on 04/10/2003 10:53:31 AM
April 10, 2003 at 5:08 pm
spdhiva:
Thank you for your suggestion. As you recommanded, I created a new SQL table and transfer data from the Excel file to this table. Basically there is the same problem. some of the text in the same row get inserted to the table, but some of them don't. Can't tell the difference between the one can and can't.
any more idea?
April 10, 2003 at 5:52 pm
Don't create table on SQL.
Set up the DTS job to create the SQL table for the excel data.When you do that it creates the table with the default data type and it transfers the data from excel to SQL tbl.Then map the new tbl with the old tbl and do the data validation and you can debug in a easy way.
Edited by - spdhiva on 04/10/2003 5:52:56 PM
April 11, 2003 at 3:36 pm
quote:
buijules:How do you change the file in a tilde delimite file? Excel does not have the option to save files into this format.
Yes...Excel has this option.
OR just remane the file.xls to file.csv
and than try to open with excel, should works.
JFB
April 14, 2003 at 4:00 am
I can load spreadsheet into table with or without commas in text, DTS is not processing the commas as delimiters.
Is the table you loading a permanent table?
If you email me the table definition and spreadsheet (if no sensitive data) and I will try to see if I get same problem.
Far away is close at hand in the images of elsewhere.
Anon.
April 14, 2003 at 11:20 am
DavidBurrows:
Thank you very much for asking for my input file and table definition to try it out. I am happy to send them to you, but I did not see a way to attach file in this forum. Do you know how?
And you are right, I also confirm that DTS is not processing the commas as delimiters. so I can load spreadsheet into table with or without commas in text.
and the table I am loading is not a permanent table. I definite it with all the columns as varchar(255) or nvarchar(255).
April 15, 2003 at 2:05 am
No I don't. If you click on my name in this list then you see my email address.
Far away is close at hand in the images of elsewhere.
Anon.
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply