do you have any idea - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

do you have any idea

Author

Topic

ELLIEMAY
Starting Member

43 Posts

Posted - 2004-12-28 : 16:37:44

...why this script is only returning the top record for only 4 of my 9 files in the directory? It should be returning all records for all nine files. Thanks in advance.

CREATE TABLE files (filename varchar(128))
INSERT INTO files (filename) EXEC master..xp_cmdshell 'dir/b "f:\Research\*.csv"'
DELETE files WHERE NullIf(filename, 'File Not Found') IS NULL

DECLARE @sql varchar(8000)
WHILE EXISTS (SELECT * FROM files)
BEGIN

SELECT @sql=''
SELECT @sql=@sql+'BULK INSERT HBOC_LIST FROM ''F:\Research\' + filename + ''' WITH (ROWTERMINATOR='',\n'', FIELDTERMINATOR='','', MAXERRORS=10); ' FROM files
DELETE files WHERE CharIndex(filename, @sql)>0
EXEC(@sql)

END

DROP TABLE files

insert into MATCH_list
SELECT a.*
FROM HBOC_LIST AS a INNER JOIN PAT_LIST AS p
ON a.ssn = p.ssn

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-12-28 : 16:41:12

I suspect that your files are formatted differently. BULK INSERT must be finding an EOF character after the first row.

Tara

nr
SQLTeam MVY

12543 Posts

Posted - 2004-12-28 : 16:43:57

well DELETE files WHERE CharIndex(filename, @sql)>0

if you have files
a.csv
helloa.csv
dba.csv

if it imports a.csv first then it won't import the others.

see
http://www.mindsdoor.net/SQLTsql/ImportTextFiles.html
also do all rows end with a ,crlf

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

ELLIEMAY
Starting Member

43 Posts

Posted - 2004-12-28 : 17:06:38

Okay, maybe this will give you a bit more information. I have a SQL in a different system running that is creating a csv file that is being FTP'ed to me daily. Lets say that I have 9 of these files in my directory after being FTP'ed. They are named like "CR_HB20041107.csv". Each one is named like that but with a different date. They are all formatted exactly the same.

I have stopped the script after createing the "files" table and it does list all 9 files.

Thanks again in advance.

Also, How do I tell what the rows end with?

nr
SQLTeam MVY

12543 Posts

Posted - 2004-12-28 : 17:20:12

>> Also, How do I tell what the rows end with?
You can look at it with a hex editor - or just import manually and see what happens.

In your script I would check to see how many rows have been imported and log that. If no rows then you probably have an error.
I do it by inserting into a temp table then insert to the main table.

Would also have files as a temp table.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-12-28 : 17:22:52

Instead of using your script, run BULK INSERT manually for each file. Does it import correctly this way?

Tara

ELLIEMAY
Starting Member

43 Posts

Posted - 2004-12-28 : 18:01:49

Okay. I did an import task to import one of the files into my table. It only worked when setting the Row terminator to LF, but then worked great for that one file. So I went back into Query analyzer and ran this:

BULK INSERT FTSM_CR_PATIENTS.dbo.[HBOC_LIST]
FROM 'f:\Research\CR_HB20041219.csv'
WITH
(
FIELDTERMINATOR = ',',
ROWTERMINATOR = '\n'
)

This runs fine, but has 0 entries when I know that it should have at least 80.

nr
SQLTeam MVY

12543 Posts

Posted - 2004-12-28 : 18:03:53

That has crlf as row terminator not lf.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

ELLIEMAY
Starting Member

43 Posts

Posted - 2005-01-06 : 16:25:11

Okay, here is what I did. I have my file generating with "|" as a row terminator now. If isn't erroring, but it is only inserting the first record(row) of the CSV into the SQL table. Any advice would be great!!!! Thanks in advance.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-01-06 : 17:09:51

Could you send the file to us via e-mail if there isn't confidential data in it? Also post the DDL for the table to be imported into for the file being sent.

Tara

ELLIEMAY
Starting Member

43 Posts

Posted - 2005-01-11 : 15:54:17

The entire file is confidential, but I can give you some example records:
111-11-1111 PLUNK BOB E 1/5/2005 10/27/1911 x0000000000 IPX |
222-22-2222 DOE JESSICA E 1/5/2005 10/31/1977 x0000000001 OTX |
333-33-3333 DOE JAMIE E 1/5/2005 1/15/1979 x0000000002 IPX |

Here are the way the tables are setup:

PAT_LIST
SSN,lname,fname,middle,study,num,notes,dob (All are VARCHAR 50 Allow Nulls)

HBOC_LIST
SSN,lname,fname,fac,adate,dob,num,type (All are VARCHAR 50 Allow Nulls)

Match_list
SSN,lname,fname,fac,adate,dob,num,type (All are VARCHAR 50 Allow Nulls except for adate - smalldate 4 Allow Nulls)

Will this help?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-01-11 : 16:00:43

No it doesn't. In order for us to help, we would need the file sent to us and not data posted here as you may be missing the rows that have the problems and also hidden characters. Since it is confidential, I'm not sure what we can do except you scrubbing the data in the file. Scrubbing it though may alter the file so that it doesn't duplicate what you are seeing.

What I would suggest doing is loading only some rows into your tables using the first and last row options (FIRSTROW, LASTROW). If only row 1 is being inserted when you do the whole thing, then try row 50-100. Repeatedly doing this for different sets should narrow it down to the problematic rows. I'm guessing that the file isn't being created the same for each row.

Tara

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources