Error in SQL insert

[Connman62]

GoanywhereXMl.txt
(1.97 KiB) Downloaded 83 times

Help Please,

I am trying to create the attached project, which is a pretty simple CSV to SQl table ETL. I have a similar project that reads the data from a SQl view and works fine. In this case I needed to use a file as the source data. The file is multi-purpose and has more fields than I need so I thought I could just specify the column number in the rowset var. But I can't seem to get past a error on the SQl insert step.

8099 - Connect to Destination DB] An unexpected error occurred. Invalid cursor position: BEFORE_FIRST Original expression: data[1].

I've tried various things like putting quotes around the rowset column var but nothing seems to work. I'm pretty much a novice with GoAnywhere so I was wondering if someone could give me some guidance.

Thanks!

[Support_Rick]

Check out the following link about inserting data into a Database Table ...

How to import CSV file into Database

You'll see what you need to do to get your rowset inserted as needed.

[saikiranm]

Rick,

When I set text qualifier to double quote the CSV load is failing.

Project XML:

Code: Select all

---

<project name="CSV to DB " mainModule="Main" version="2.0" logLevel="verbose">
   <description>CSV to DB </description>
   <module name="Main">
      <readCSV label="Read CSV" inputFile="resource:smb://filedrop/Test/Test.csv" outputRowSetVariable="CSVFile" fieldDelimiter="comma" skipInvalidRecords="false" skipFirstRow="true" recordDelimiter="CRLF" textQualifier="double quotes" version="1.0" />
      <sql label="Connect to DB2" resourceId="DB" version="1.0">
         <query label="Insert Data" inputRowSetVariable="${CSVFile}">
            <statement>INSERT INTO gaTestCSV( ID, Name)
Values(?,?)
               </statement>
         </query>
      </sql>
   </module>
</project>


Test.csv:
ID, Name
1, Sai Kiran
2, "Vijay, Balusu"

Job Log:
12/21/16 2:40:40 AM INFO Executing module 'Main'
12/21/16 2:40:40 AM INFO Executing task 'readCSV 1.0 (Read CSV)'
12/21/16 2:40:41 AM INFO Data parsed successfully and the rowset variable 'CSVFile' was created
12/21/16 2:40:41 AM INFO Finished task 'readCSV 1.0 (Read CSV)'
12/21/16 2:40:41 AM INFO Executing task 'sql 1.0 (Connect to DB2)'
12/21/16 2:40:42 AM INFO Executing sub-task 'query'
12/21/16 2:40:42 AM INFO Executing statement - INSERT INTO gaTestCSV( ID, Name)
Values(?,?)

12/21/16 2:40:42 AM INFO Opening file 'resource:smb://filedrop/Test/Test.csv'
12/21/16 2:40:42 AM ERROR [9013 - Read CSV] resource:smb://filedrop/Test/Test.csv: Invalid record was found with boundaries '3, 0, 3, 18'. Invalid character '"' at line '3' and column '4. The field must be enclosed with in '"' characters.
12/21/16 2:40:42 AM ERROR [8044 - Connect to DB2] Last known record near boundaries (start line: 3, start column: 0, end line: 3, end column: 18) of file 'resource:smb://filedrop/Test/Test.csv'
12/21/16 2:40:42 AM ERROR [8098 - Connect to DB2] [9007 - Read CSV] resource:smb://filedrop/Test/Test.csv: Invalid record was found. Full stack trace written to '1000000000634_error_1.log'
12/21/16 2:40:42 AM INFO Finished project 'CSV to DB '
12/21/16 2:40:42 AM ERROR [8098 - Connect to DB2] [9007 - Read CSV] resource:smb://filedrop/Test/Test.csv: Invalid record was found
12/21/16 2:40:42 AM INFO End Date and Time: 12/21/16 2:40:42 AM



Thanks,
Kiran

[Support_Rick]

Kiran,

Remove the "space" between the comma "," and the value. Then, try again.

Should be:

ID,Name
1,Sai Kiran
2,"Vijay, Balusu"

[falak]

Im new to go anywhere...I want to create new database table by reading csv file and inserting data into a Database Table ...In csv file i have 1400 colums ..

[Support_Rick]

Just execute the SQL Task and add your SQL Statement to that task ...

SELECT * INTO MYNEWTABLE FROM MYSOURCETABLE

Just like any other T-SQL or SQL Statement

[falak]

I'll Create table abc, table abc have 1422 columns ,now i want to insert data in table abc reading from file.cvs.
I use two csv files(header and csvdata),header for column that have one row (headers) and csvdata for insert rows/data. I used sql for insertion
insert into abc (${header[head]}) values ('${csvdata[data]}') ...head and data are the iterative variables
Data is inserted into table but in one column.
Suggest me how i insert data in table from file?

[Support_Rick]

Doing a search on the GoAnywhere Forum for "CreateCSV" you'll find some articles that will help you with this...

can-i-change-the-headings-in-a-csv-file-635
writing-to-csv-or-flat-file-without-rowset-852

Please review those and you should see how it works

[falak]

I am really sorry ,i can't explain my problem correctly..Actually i need to load thousands records in table from csv file , against 1400 columns in a table .
Secondly When i used load
LOAD DATA INFILE 'read10.csv'
INTO TABLE goanywhere
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 LINES;
Error:[8098 - LOAD Data from file] File 'F:\Data\ldatabase\read10.csv' not found (Errcode: 2 - No such file or directory)
If i put file in thate directory ,then data will load,otherwise this function not get file automatically

[Support_Rick]

Falak,

Please review the "READCSV" task. It reads the csv file into a Rowset. Then, you use that Rowset to insert data into your Database. You do not have to do the "load data" command.