topic Re: new to Talend - create tables using flat file in Talend Studio

new to Talend - create tables using flat file

_AnonymousUser — Fri, 25 Apr 2014 05:11:41 GMT

Hi everyone, take it easy on my here, this is my first go at any sort of "ETL" tool.
In a nutshell I would like to take a single flat file that contains groups of records and create a shell for a baseline SQL Server environment, tables based on the field name and lenghts and continue to populate the tables with daily files.
For startes I would like to know where to start in the studio, I have my flat text file sitting here...how can I use Talend to set the field lenght positions to populate my SQL DB?
Im sure im wording this poorly, ask me questions and I can respond.
Thanks!
J

Re: new to Talend - create tables using flat file

Anonymous — Fri, 25 Apr 2014 06:36:47 GMT

Hi J,
Have you created repository metadata for your input file, if not can you please create it...
Pl check tutorial - http://www.talendforge.org/tutorials/tutorial.php?idTuto=5
Also look for similar tutorial to load data into the SQL DB.
Thanks
Vaibhav

Re: new to Talend - create tables using flat file

Anonymous — Fri, 25 Apr 2014 06:46:41 GMT

Please refer to "Getting.Started.with.Talend.Open.Studio.for.Data.Integration" book, it will explain you all the concepts step by step and the 1st chapter only deals with how to handle files.
Have a happy learning!!

Re: new to Talend - create tables using flat file

Anonymous — Fri, 25 Apr 2014 08:18:11 GMT

Hi,
Could you please elaborate your case with an example with input and expected output values?
Best regards
Sabrina

Re: new to Talend - create tables using flat file

_AnonymousUser — Fri, 25 Apr 2014 20:25:15 GMT

Hi J,
Have you created repository metadata for your input file, if not can you please create it...
Pl check tutorial - http://www.talendforge.org/tutorials/tutorial.php?idTuto=5
Also look for similar tutorial to load data into the SQL DB.
Thanks
Vaibhav

Thank you, I have gone through this tutorial and created meta data for my file.
My one flat file contains 3 different types of records (0, 2 and 4 record), each in their own layout. I created metadata to match each one. Thank you!
J

Re: new to Talend - create tables using flat file

_AnonymousUser — Fri, 25 Apr 2014 20:37:15 GMT

I have also successfully connected to my remote SQL Server and can see my databases!
Now I need to figure out how to use my new metadata to create a new DB.
J

Re: new to Talend - create tables using flat file

Anonymous — Mon, 28 Apr 2014 04:29:09 GMT

Hi jfbroede,

I have also successfully connected to my remote SQL Server and can see my databases!
Now I need to figure out how to use my new metadata to create a new DB.

Please have a look at online document TalendHelpCenteretting up a database connection.
Best regards
Sabrina

Re: new to Talend - create tables using flat file

_AnonymousUser — Sat, 03 May 2014 19:16:00 GMT

Hi jfbroede,

I have also successfully connected to my remote SQL Server and can see my databases!
Now I need to figure out how to use my new metadata to create a new DB.

Please have a look at online document TalendHelpCenteretting up a database connection.
Best regards
Sabrina

I have made the connection. But Im still having issues adding/populating the SQL DB.
I have the outputdelimited file, and when I try and run a job to send it to my SQL DB I get this:
Exception in component tMSSqlOutput_1
java.sql.SQLException: An object or column name is missing or empty. For SELECT INTO statements, verify each column has a name. For other statements, look for empty alias names. Aliases defined as "" or [] are not allowed. Change the alias to a valid name.
at net.sourceforge.jtds.jdbc.SQLDiagnostic.addDiagnostic(SQLDiagnostic.java:368)
at net.sourceforge.jtds.jdbc.TdsCore.tdsErrorToken(TdsCore.java:2820)
at net.sourceforge.jtds.jdbc.TdsCore.nextToken(TdsCore.java:2258)
at net.sourceforge.jtds.jdbc.TdsCore.getMoreResults(TdsCore.java:632)
at net.sourceforge.jtds.jdbc.JtdsStatement.processResults(JtdsStatement.java:584)
at net.sourceforge.jtds.jdbc.JtdsStatement.executeSQL(JtdsStatement.java:546)
at net.sourceforge.jtds.jdbc.JtdsStatement.executeImpl(JtdsStatement.java:723)
at net.sourceforge.jtds.jdbc.JtdsStatement.execute(JtdsStatement.java:1160)
at jeff_test.how_to_setup_join_link_0_1.How_To_Setup_join_link.tFileInputDelimited_1Process(How_To_Setup_join_link.java:1546)
disconnected
at jeff_test.how_to_setup_join_link_0_1.How_To_Setup_join_link.runJobInTOS(How_To_Setup_join_link.java:3090)
at jeff_test.how_to_setup_join_link_0_1.How_To_Setup_join_link.main(How_To_Setup_join_link.java:2955)
Job How_To_Setup_join_link ended at 14:11 03/05/2014.

What am I missing?
Jeff

Re: new to Talend - create tables using flat file

Anonymous — Sun, 04 May 2014 05:34:32 GMT

Hi,
Would you mind posting your job design screenshots into forum? What's your sql query?
Best regards
Sabrina

Re: new to Talend - create tables using flat file

Anonymous — Mon, 05 May 2014 19:17:09 GMT

I am also fairly new to Talend, so these are some recommendations from one beginner to another.
1. Start by just loading the flat file and make sure you can write that to a log.
tFileInputDelimited -> tLogFile
If the input file has a lot of records, you can set the Limit parameter on the tFile... so it only reads the first 100 or so.
2. Gradually add components, but continue to end in the tLogFile
Add a tMap: tFileInputDelimited -> tMap -> tLogFile
This will let you play with different settings in tMap. Using filters, expressions, etc.
3. Update the output of your tMap to match what you have in your database, but still write to a tLogFile
If your flat file is reading numbers as string but your database field is set to int, you can play with conversions within tMap. You can fix any conversion problems before connecting your database.
4. Once you are seeing the type of output in your tLogFile that you expect to see in your database, then connect the tMySQLOutput (or whatever database you are using).
Doing the step by step has helped me a lot as I figure out how to use this tool. when I thought I had it figured out and just wrote the complete process at the start, I had no idea where to look for errors or what was going on. Doing it this way allowed me to troubleshoot each component individually so I could 1) focus only on the settings of 1 component instead of the entire job and 2) really understand what the component was really doing which helped in tweaking things later.
I'm also new to Java so I have had to google a lot about Java contexts. Doing a Google search on "Java string to int" has helped me find things like Integer.parseInt(row1.PageLookups.replace(",","")) that I could throw right into tMap.

Re: new to Talend - create tables using flat file

Anonymous — Tue, 06 May 2014 05:49:19 GMT

Hi Jeff,
This is problem with the query used in input component? Can you pl put a screenshot of your job and a query used?
Vaibhav

Re: new to Talend - create tables using flat file

Anonymous — Tue, 26 Apr 2016 11:37:31 GMT

Hi Jeff,

Please find my screen shot i am getting error as
Exception in component tMSSqlInput_1
java.sql.SQLException: An object or column name is missing or empty. For SELECT INTO statements, verify each column has a name. For other statements, look for empty alias names. Aliases defined as "" or [] are not allowed. Change the alias to a valid name.

Please revert me to mail : shivi.bhatt@igate.com
please help on this
Thanks
Shivi Bhatt