Merge CSV to SQL

Discuss RoboTask here
Post Reply
deesloop
Posts: 70
Joined: Wed Feb 25, 2009 3:44 am

Merge CSV to SQL

Post by deesloop »

I'm looking to automate the merge of a CSV file into SQL, but my DB EXEC SQL step is just not working.

Provider=SQLOLEDB.1;Password=sdfsadfasf;Persist Security Info=True;User ID=sa;Initial Catalog=TabletForms;Data Source=befgon

BULK INSERT Ages
FROM 'c:\Book2.csv'
WITH
(
FIRSTROW = 2,
FIELDTERMINATOR = ',',
ROWTERMINATOR = '\n'
)
GO


Initially I got a file not found, but then sorted it as it would seem the file has to be the server that is running SQL not the server running Robotask
However I get a "Cannot bulk load because the maximum number of errors (10) was exceeded" The data is validated as legitimate.
Oleg
Site Admin
Posts: 3000
Joined: Thu Jan 01, 1970 1:00 am
Contact:

Re: Merge CSV to SQL

Post by Oleg »

Read more about BULK INSERT here: https://docs.microsoft.com/en-us/sql/t- ... ansact-sql
I think that you don't need to specify ROWTERMINATOR, because it is "\r\n" by default

Regarding errors:
Unfortunately I can't say something. Maybe your file contains some incorrect data. But apparently that some errors are exist.
It's necessary to analyze your CSV file and the table structure to say more.


But I prefer to use text loop and insert row-by-row in the loop. In this case you can correct the file structure and use fields in any order.
Oleg Yershov
deesloop
Posts: 70
Joined: Wed Feb 25, 2009 3:44 am

Re: Merge CSV to SQL

Post by deesloop »

Thanks Oleg, you were right.
While the data is fine, it's the structure of the table
It's a 3 table with 3 fields, one being a unique ID whereas the CSV has 2 fields.
I had hoped the ID would be created automatically but apparently not.

So looks like I'm having to read the csv a line at a time and dump into SQL
Looking to find out how to start from line 2 as the CSV file has column headers

Have you any examples of doing a line at a time post into SQL?

Thank you
Oleg
Site Admin
Posts: 3000
Joined: Thu Jan 01, 1970 1:00 am
Contact:

Re: Merge CSV to SQL

Post by Oleg »

look at my example below. This task processes CSV text fro 2-nd line and skip 1-st line
Of course you can read the text from a file

Code: Select all

;*****************************
;* RoboTask Task file
;* Do not edit in text editor!
;*****************************
 
[Root]
ActionAfterRun=INTEGER|0
Actions=FOLDER
Automat=INTEGER|-1
CatID=INTEGER|444033236
Comment=STRINGLIST
ContinueOnError=INTEGER|0
ExternalName=STRING|"Task161"
Hide=INTEGER|0
ID=INTEGER|1651758159
LogOnAsUser=INTEGER|1
Name=STRING|"process CSV from 2-nd row"
OnErrorTaskID=INTEGER|-1
Priority=INTEGER|3
RunOnClose=INTEGER|0
RunOnStartup=INTEGER|0
ToLog=INTEGER|3
UnicodeFormat=INTEGER|1
WriteGeneralLog=INTEGER|0

[Actions]
Action1=FOLDER
Action2=FOLDER
Action3=FOLDER
Action4=FOLDER
Action5=FOLDER
Action6=FOLDER
Action7=FOLDER

[Actions\Action1]
ActionID=STRING|"A_VARIABLES_SET"
Enabled=INTEGER|-1
Name=STRING|"Set variable ""NUM"" with value ""1"""
Params=FOLDER

[Actions\Action1\Params]
expand=STRING|"0"
varname=STRING|"NUM"
varvalue=STRING|"1"

[Actions\Action2]
ActionID=STRING|"A_LOOP_TEXT"
Enabled=INTEGER|-1
Name=STRING|"Text Loop"
Params=FOLDER

[Actions\Action2\Params]
destvar=STRING|"LINE"
line0=STRING|"Fild1,Field2,Field3"
line1=STRING|"""Row1 Field1"",""Row1 Field2"",""Row1 Field3"""
line2=STRING|"""Row2 Field1"",""Row2 Field2"",""Row2 Field3"""
line3=STRING|"""Row3 Field1"",""Row3 Field2"",""Row3 Field3"""
linecount=STRING|"4"
sourcetext=STRING|"1"

[Actions\Action3]
ActionID=STRING|"A_FLOW_IF"
Enabled=INTEGER|-1
Name=STRING|"If Then"
Params=FOLDER

[Actions\Action3\Params]
case=STRING|"0"
connection=STRING|"0"
count=STRING|"1"
operator=STRING|"3"
type=STRING|"1"
value1=STRING|"{num}"
value2=STRING|"1"

[Actions\Action4]
ActionID=STRING|"A_DIALOG_MESSAGE"
Enabled=INTEGER|-1
Name=STRING|"Show ""Line = {Line}"""
Params=FOLDER

[Actions\Action4\Params]
icon=STRING|"1"
msg0=STRING|"Line = {Line}"
msg1=STRING|"Field1 = {Line(0)}"
msg2=STRING|"Field2 = {Line(1)}"
msg3=STRING|"Field3 = {Line(2)}"
msgcount=STRING|"4"
playsound=STRING|"0"
showmessage=STRING|"1"

[Actions\Action5]
ActionID=STRING|"A_FLOW_ENDIF"
Enabled=INTEGER|-1
Name=STRING|"End If"

[Actions\Action6]
ActionID=STRING|"A_VARIABLES_INCREMENT"
Enabled=INTEGER|-1
Name=STRING|"Increment variable ""num"""
Params=FOLDER

[Actions\Action6\Params]
incement=STRING|"1"
vaiable=STRING|"num"

[Actions\Action7]
ActionID=STRING|"A_LOOP_END"
Enabled=INTEGER|-1
Name=STRING|"End Loop"

Save the text of the task to a file and use menu Task->Import to import the task into RoboTask.
Oleg Yershov
deesloop
Posts: 70
Joined: Wed Feb 25, 2009 3:44 am

Re: Merge CSV to SQL

Post by deesloop »

OK, I see what it's doing and I can read a line at a time from the CSV,
I'd never used a field as an array, so missed a trick there!

I got it all working thanks and appreciate the straightforwardness of your solution.
Post Reply