×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

SQL 2000 Import Question

SQL 2000 Import Question

SQL 2000 Import Question

(OP)
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.

RE: SQL 2000 Import Question

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)
noevil
http://www.mrdenny.com

RE: SQL 2000 Import Question

(OP)
DTS Import

RE: SQL 2000 Import Question

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

RE: SQL 2000 Import Question

(OP)
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...

RE: SQL 2000 Import Question

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

RE: SQL 2000 Import Question

(OP)
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.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close