Error in SQL insert

Post any question you may have in regards to GoAnywhere MFT and let our talented support staff and other users assist you.
If you need a quicker response, please create a support ticket via the customer portal my.goanywhere.com or contact our support team by email at [email protected].
11 posts Page 1 of 2

Connman62

Posts: 1
Joined: Wed Nov 16, 2016 12:32 pm

Post by Connman62 » Wed Nov 16, 2016 1:07 pm
GoanywhereXMl.txt
(1.97 KiB) Downloaded 847 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

Support Specialist
Posts: 590
Joined: Tue Jul 17, 2012 2:12 pm
Location: Phoenix, AZ

Post by Support_Rick » Tue Nov 29, 2016 5:21 pm
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.
Rick Elliott
Lead Solutions Consultant
(402) 944.4242
(800) 949-4696

saikiranm

Posts: 2
Joined: Wed Dec 07, 2016 8:56 am

Post by saikiranm » Wed Dec 21, 2016 5:42 am
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

Support Specialist
Posts: 590
Joined: Tue Jul 17, 2012 2:12 pm
Location: Phoenix, AZ

Post by Support_Rick » Tue Dec 27, 2016 11:41 am
Kiran,

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

Should be:

ID,Name
1,Sai Kiran
2,"Vijay, Balusu"
Rick Elliott
Lead Solutions Consultant
(402) 944.4242
(800) 949-4696

falak

Posts: 8
Joined: Tue Dec 27, 2016 3:03 am

Post by falak » Tue Jan 03, 2017 7:15 am
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

Support Specialist
Posts: 590
Joined: Tue Jul 17, 2012 2:12 pm
Location: Phoenix, AZ

Post by Support_Rick » Wed Jan 04, 2017 9:23 am
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
Rick Elliott
Lead Solutions Consultant
(402) 944.4242
(800) 949-4696

falak

Posts: 8
Joined: Tue Dec 27, 2016 3:03 am

Post by falak » Mon Jan 16, 2017 4:25 am
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

Support Specialist
Posts: 590
Joined: Tue Jul 17, 2012 2:12 pm
Location: Phoenix, AZ

Post by Support_Rick » Mon Jan 16, 2017 8:19 am
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
Rick Elliott
Lead Solutions Consultant
(402) 944.4242
(800) 949-4696

falak

Posts: 8
Joined: Tue Dec 27, 2016 3:03 am

Post by falak » Tue Jan 17, 2017 3:35 am
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

Support Specialist
Posts: 590
Joined: Tue Jul 17, 2012 2:12 pm
Location: Phoenix, AZ

Post by Support_Rick » Tue Jan 17, 2017 9:14 am
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.
Rick Elliott
Lead Solutions Consultant
(402) 944.4242
(800) 949-4696
11 posts Page 1 of 2