Incorrect syntax when I try to load many csv files

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

General SQL Server Forums

New to SQL Server Programming

Incorrect syntax when I try to load many csv files

Author

Topic

lordoftrades
Starting Member

15 Posts

Posted - 2012-08-29 : 13:47:25

Hi

I just cannot find the error, and I have ONE single line.... Maybe thats the reason?

I try to load many csv files, and got this line:


for %a in (c:\Users\Espen\Desktop\Intraday\Textfiles\IQ\Intraday\*.csv) do bcp testminidatabase..CSVTest4 in %a -T -SESPENS-NYE-PC\SQLEXPRESS -c

And the error message is:
Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'for'

I'm a total newbie here, so this might be very obvious. But to me its like Chinese.

Kind regards
Espen

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-08-29 : 13:58:16

You would need to use xp_cmdshell as in

xp_cmdshell  'for %a in (c:\Users\Espen\Desktop\Intraday\Textfiles\IQ\Intraday\*.csv) do bcp testminidatabase..CSVTest4 in %a -T -SESPENS-NYE-PC\SQLEXPRESS -c'

Also, if not already enabled, xp_cmdshell would need to be enabled - by default it is turned off, I think. http://msdn.microsoft.com/en-us/library/ms175046.aspx

Alternatively, you can run the command you posted from a command window (on the server box).

lordoftrades
Starting Member

15 Posts

Posted - 2012-08-29 : 14:07:15

quote:
Originally posted by sunitabeck

You would need to use xp_cmdshell as in
xp_cmdshell 'for %a in (c:\Users\Espen\Desktop\Intraday\Textfiles\IQ\Intraday\*.csv) do bcp testminidatabase..CSVTest4 in %a -T -SESPENS-NYE-PC\SQLEXPRESS -c'

Also, if not already enabled, xp_cmdshell would need to be enabled - by default it is turned off, I think. http://msdn.microsoft.com/en-us/library/ms175046.aspx

Alternatively, you can run the command you posted from a command window (on the server box).

Ahh, that makes more sense. I ran a code earlier today that I think activated the xp_cmdshell.

When I run the code now, I get NULL as output result......?
[CODE]
Auto-attach to process '[1912] [SQL] ESPENS-NYE-PC' on machine 'ESPENS-NYE-PC' succeeded.
The thread 'ESPENS-NYE-PC\SQLEXPRESS [53]' (0x1180) has exited with code 0 (0x0).
The thread 'ESPENS-NYE-PC\SQLEXPRESS [53]' (0x1180) has exited with code 0 (0x0).
The program '[1912] [SQL] ESPENS-NYE-PC: ESPENS-NYE-PC\SQLEXPRESS' has exited with code 0 (0x0).[/CODE]

It says "Query executed successfully" furter down though.

xhostx
Constraint Violating Yak Guru

277 Posts

Posted - 2012-08-29 : 14:15:31

[code]I have tested this, it worked for me:

FOR %i IN (C:\folder1\folder2\*.txt) DO bcp DBNAME..YOURTABLE in %i /c /Ulogin /Ppass /r\n[/code]

--------------------------
Joins are what RDBMS's do for a living

lordoftrades
Starting Member

15 Posts

Posted - 2012-08-29 : 16:48:26

quote:
Originally posted by xhostx

I have tested this, it worked for me:

FOR %i IN (C:\folder1\folder2\*.txt) DO bcp DBNAME..YOURTABLE in %i /c /Ulogin /Ppass /r\n

--------------------------
Joins are what RDBMS's do for a living

Hmm, I still cannot get it working. Now I get a heavy list that repeat itsself:
[CODE]
NULL
C:\Windows\system32>bcp testminidatabase..CSVTest4 in c:\Intraday\OPEN_5.csv -T -SESPENS-NYE-PC\SQLEXPRESS -c
NULL
Starting copy...
SQLState = S1000, NativeError = 0
Error = [Microsoft][SQL Server Native Client 11.0]Unexpected EOF encountered in BCP data-file
NULL
0 rows copied.
Network packet size (bytes): 4096
[/CODE]

I just don't get it. I also tried to run this from command prompt, but it was not giving much result. I just get told that:

EXEC is not recogonized as internal or external command..... etc etc.

If I try to skip the EXEC master, I get the same answer just for xp_cmdshell is not recognized.....

I'm sorry for being a bit difficult now, but I really would like to get to the bottom of this.

Kind regards
Espen

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-08-29 : 21:24:23

xp_cmdshell is a SQL procedure that executes the parameter as though it were executed in a command window. For example, in a command window you might type the dir command; to do the same thing from SQL you would do xp_cmdshell 'dir'.

Regardless, I think the error message you are getting indicates that the file is not in the expected format. Usually that happens when the field terminator or row terminator are not the expected values.

To see what the problem is, start out by creating a file with single row in it in the format you think it should be and then try to load that. If that succeeds, then try to load one of the files instead of all the files. If that does not succeed then you can compare its format against the data in your actual files.

xhostx
Constraint Violating Yak Guru

277 Posts

Posted - 2012-08-30 : 09:50:55

[code]I think you need to break it down.
I would advice you first think of every command and tools and know how they work all together.
1. for your knowledge: Bcp is different from BULK insert!!! Think of this.
2. if you are copying data from a csv or text file using a bcp.....you probably need to have a ONE COLUMN TABLE.
3. Check the file it self, copy a piece of your data to a test.csv and examine it.
Note: I had to resolve the same problem where the text delimiters weren't properly embedded into every line[/code]

--------------------------
Joins are what RDBMS's do for a living

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources