Welcome Guest! Log in

Topic-icon Question Inserting just one line in a table

More
27 May 2015 10:18 - 27 May 2015 12:25 #1 by J.M
Hi,
I have a ‘csv’ file in which there is some Data but I want insert in a table (in PostgreSQL) just the first line.
I tried that



But it did not work
Is it there any other solution !
Attachments:
Last edit: 27 May 2015 12:25 by J.M.

Please Log in or Create an account to join the conversation.

More
27 May 2015 14:10 #2 by Thomas BLETON
Replied by Thomas BLETON on topic Inserting just one line in a table
Hello,
Can you please show what error you get ?
Which step of the generated process fails ?
Note : you may have to map the target fields on the Staging Area, because they have SQL expressions which ned to be executed on a rdbms. They are currently set on Source.

Please Log in or Create an account to join the conversation.

More
27 May 2015 15:59 - 27 May 2015 16:26 #3 by J.M
Replied by J.M on topic Inserting just one line in a table
Hi,
I got the same Error ( with or with out the Staging Area):



Attachments:
Last edit: 27 May 2015 16:26 by J.M.

Please Log in or Create an account to join the conversation.

More
27 May 2015 16:45 #4 by Thomas BLETON
Replied by Thomas BLETON on topic Inserting just one line in a table
This message is raised by Postgresql because the COPY utility tries to load data from the file but it contains extra data after the last column.
For this load to work, the file must have the same structure on every one.
Or you can create a multi-record metadata file if the lines have different structures.

Please Log in or Create an account to join the conversation.

More
27 May 2015 16:58 #5 by J.M
Replied by J.M on topic Inserting just one line in a table
I need to insert just the first line and I used a filter for that !

Please Log in or Create an account to join the conversation.

More
27 May 2015 17:34 #6 by Thomas BLETON
Replied by Thomas BLETON on topic Inserting just one line in a table
The source data is a file.
On the current versions of Stambia, transformations and filters cannot be applied directly on files. The file has to be loaded into a database table first, entirely.
Your mapping is correctly set : the filter is set on the Staging Area.

What you need to find out is why the COPY fails.

What is the code in the "Load into target" action ("Step details" view) ?
What is the File structure in the medata ?
Can you provide the first lines of data ?

Please Log in or Create an account to join the conversation.

More
27 May 2015 20:50 #7 by J.M
Replied by J.M on topic Inserting just one line in a table
Here is the code in the "Load into target"


the structure in my metadata is:
Table(
Date_from date,
Date_to date,
champ1 varchar(3),
champ2 varchar(30),
champ3 varchar(10),
champ4 decimal,
champ5 decimal
);

the first line in the file must be insert in Date_form and Date_to and it must be repeated as much as I have Data ( I will create 2 mappings; one for Dates and the other for the other data and I ll create a process to repeat the first mapping.That is my idea )
here the first lines :
HEAD12/22/2014 To 01/28/2015
AED,UAE dirham,per USD,3.67304,4.34976
ARS,Argentine peso,per USD,8.57102,10.15014

for example here as output I must have:
12/22/2014 01/28/2015 AED UAE dirham per USD 3.67304 4.34976
12/22/2014 01/28/2015 ARS Argentine peso per USD 8.57102 10.15014
Attachments:

Please Log in or Create an account to join the conversation.

More
28 May 2015 15:07 #8 by Thomas BLETON
Replied by Thomas BLETON on topic Inserting just one line in a table
This file is interesting; It looks simple but the structure is complex :
- The first line has a POSITIONAL structure
- The lines of data are DELIMITED, and they have a different structure from the first line

The solution in Stambia is to use a "multi-record file" metadata. This kind of metadata will help you read from either the first line, or the data lines.
Here is an example of Metadata file + sample file, which will help you understand how it can be used.

The file is described in 2 records: the Head record, for the first line, and the Data record, for the rest of the file (see the Filters in the metadata).
The mapping has a cross join between these two records.
The target fields are mapped on the Staging Area.

Please compile this process, and open the steps to see how it works. This will help you understand.
I hope this helps.

This message has attachments files.
Please log in or register to see it.

Please Log in or Create an account to join the conversation.

More
28 May 2015 15:18 - 28 May 2015 15:18 #9 by Thomas BLETON
Replied by Thomas BLETON on topic Inserting just one line in a table
One more thing : in the mapping, I volunteerly selected the "Load File to Rdbms" template, because the "Load ... with COPY" cannot work with this file, as stated above.
Last edit: 28 May 2015 15:18 by Thomas BLETON.

Please Log in or Create an account to join the conversation.

More
29 May 2015 10:39 #10 by J.M
Replied by J.M on topic Inserting just one line in a table
Thanks a lot for your help
But here is what it gives me
Attachments:

Please Log in or Create an account to join the conversation.