December 01, 2020, 08:19:57 pm
News: If someone gives you crap then give them a Bad Star
Pages: [1]   Go Down
Author Topic: Reading Comma Delimited File into MSSQL 2005 Express Database using SQL only  (Read 6064 times)
Jason Reed
Administrator
*

Stars: +4/-0
Offline Offline

Gender: Male
Posts: 4545

Pure Evil Administrator


WWW
« on: October 18, 2007, 10:08:05 am »

I need all you Microsoft SQL GURUs out there to give me a hand.

An application that we distribute decided it doesn't like sending Arabic characters to the database. I'm not sure why this is but that is what is happening.

So what I need to know is this: Is it possible for MSSQL using SQL commands to open a Text File read the contents and place the information in the table as directed with little or no help, or will it be easier if I just come up with a small app to do this?
Logged

-- signature --

simmo
Administrator
*

Stars: +3/-0
Offline Offline

Gender: Male
Posts: 8560



« Reply #1 on: October 18, 2007, 01:41:29 pm »

AFAIK SQL itself cannot, you would have to do from the management studio.
But something that I do a lot is use search and replace to turn the CSV into a bunch of sql statements. Something like this:

1 in the find box put [new line] (i use edit plus, is "\n" there)
2 in the replace with box put ") \n INSERT INTO tblTablename (fldOne, fldTwo, fldThree) VALUES ("
Logged

                 ..... .....

Jason Reed
Administrator
*

Stars: +4/-0
Offline Offline

Gender: Male
Posts: 4545

Pure Evil Administrator


WWW
« Reply #2 on: October 18, 2007, 02:01:01 pm »

hmm that is an idea as well.

However, I need it to be pretty idiot proof. So I'm going to write a small side app to take care of the import operations. If I can get MSSQL 2005 Server Express installed on my laptop grrr
Logged

-- signature --

wrack
Master of TMEOotAP
*

Stars: +3/-0
Offline Offline

Posts: 11116


Don't look back. Look in the mirror to look back.


WWW
« Reply #3 on: October 18, 2007, 11:02:55 pm »

Better off with a small app to do it for you Sad
Logged

Jason Reed
Administrator
*

Stars: +4/-0
Offline Offline

Gender: Male
Posts: 4545

Pure Evil Administrator


WWW
« Reply #4 on: October 19, 2007, 05:37:06 am »

The good thing with the small app idea is that the connection to the database is normally done over ODBC with a System DSN.

The bad thing is that I've never attempted to connect to a database this way before. However, it would be the better way because I don't need to do all that authentication crap (at least I don't think I will).
Logged

-- signature --

Martin Wallace
Founders
*

Stars: +1/-0
Offline Offline

Posts: 3602


God is dead, I have surpassed him.


WWW
« Reply #5 on: October 19, 2007, 09:50:08 am »

[meatloaf]Stop right there, before you go any further[/meatloaf]

Use the following SQL to bulk import a CSV file:

BULK INSERT mytable FROM 'filename.txt' WITH (FIELDTERMINATOR = '","')

You may have to change the FIELDTERMINATOR part (but the docs should help you with the syntax) and you may have to insert it into an intermediate table if you need to do additional processing before putting in the right place.
Logged

Every time god kills an angel, I masturbate.

Jason Reed
Administrator
*

Stars: +4/-0
Offline Offline

Gender: Male
Posts: 4545

Pure Evil Administrator


WWW
« Reply #6 on: October 19, 2007, 11:13:02 am »

Not a perfect fix actually.

This is because the brilliant minds of BAE have two fields in the text file (which is exported from their software) swapped with two fields in the database. So basically in the Database you have FIELD 1,FIELD 2,FIELD 3 while in the text file you have FIELD 1,FIELD 3,FIELD 2

I really would like to smack these guys in the back of the head for the application and database planning grrr

However, the small app is working so far. I just have to do some tinkering to ensure that when the information is loaded into the database that it doesn't break any thing else in the data layout.
Logged

-- signature --

simmo
Administrator
*

Stars: +3/-0
Offline Offline

Gender: Male
Posts: 8560



« Reply #7 on: October 19, 2007, 01:27:59 pm »

can you have the CSV re-exported?
Logged

                 ..... .....

Jason Reed
Administrator
*

Stars: +4/-0
Offline Offline

Gender: Male
Posts: 4545

Pure Evil Administrator


WWW
« Reply #8 on: October 19, 2007, 01:36:38 pm »

Well the problem is that this must be compatible with the files that the customer already has (which were exported by the software as CSV). The software does this stupid flip flop between the file and the database table. Technically the customer shouldn't play with the database (that is usually a really bad idea), so I am building an app to do the import that the software can't do right now (because of encoding issues it wants ASCII the customer data is UNICODE), but I can't be playing around with changing the customer's files because they are backwards compatible with another version of the software that they use as well.

I've got everything so far. Just checking that I don't break any of data links. For example items in one table are referenced by items in another table. If I change the item id (automatic numbering) without refreshing the items in the other tables then the software will barf, gag, and become a spaz (basically won't work right). It is a little tricky and would be much better if I didn't have so many interruptions from people asking me how things work (I feel like some sort of ducking encyclopedia for this software sometimes).
Logged

-- signature --

Pages: [1]   Go Up
Print
 
Jump to: