SQL 2000 Import Question

[Qmoto]

I've got a excel file I need to upload to the SQL server; seems simple right?

However, the comments field in the file I’m trying up import has tabs (or some other special character) and every time I try and do an import, major amounts of data get lost.

How can I get the data in cleanly without loosing anything?



NOTE: I've also tried saving the file in various text formats, as well as delimited CSV formats, still no luck importing the file.

 

[mrdenny]

What method are you using to import the data?

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[Qmoto]

DTS Import

 

[SQLSister]

Personally I've leraned to never ever import an Excel file. We always copy all the data into a text file first and then import it. THe Excel conversion does some flaky thing s like strip the leading zeros off of zip codes becasue it winterpret them as numbers even though they are clearly formatted as text in Excel. I've also had problesm with fields like part number which might be all numbers or might be all alpha or might be alpha numeric. It will bring in the number if that is the first line of the table and then fail when it ihits the first alpha.

Questions about posting. See faq183-874
Click here to help with Hurricane Relief

 

[Qmoto]

What other options do I have?

How do you create your text files? I've tried saving the document in text formats, but I end up with the same problem...

 

[SQLSister]

If you know what characters are causing your problem, you could try stripping them out use=ing a batch file that uses regular expressions. I've had to do this a couple of times when wierd junk was in the feed. I don;t write teh regular expressions so I can;t help you out there, but usually I can find a handy developer to do so when I need it. Then they run a vbscript (or whateverlanguage they like) that cleans the junk out for you before you do the import. An activeX task in DTS can be set up to run this poreprocessing.

Questions about posting. See faq183-874
Click here to help with Hurricane Relief

 

[Qmoto]

Well, I feel just a bit 'duh'... The data was actually importing fine, but I was not expanding the height of the result rows to see the rest.

Grr - that's the last time I make that mistake!

By the way, thanks for the idea of using regular expressions, I hadn't thought of that but I can see how that would be exceptionally helpful.

Ohh, and did you know that the find and replace feature in SQL 2005 Management Studio (the replacement for Enterprise Manager and Query Analyzer) allows for regular expressions - and you can specify what types of file to parse!

I've used the basic find and replace on an entire directory of mass transfer files and it was awesome!

Again, thanks for the help.