From Thursday, July, 9, 3:00 PM Pacific,
our Community site will be in
read-only mode
through Sunday, July 12th.
Thank you for your patience.

Converting Excel files to CSV and then rename file with date

Highlighted
Five Stars

Converting Excel files to CSV and then rename file with date

Hello,

I need to run though a folder,convert all the excel files to CSV and save these CSV files in a folder named after the current date.These csv files must be renamed in HHmm format(a file per minute).

Here is my job:

tFileList_1 --> tFileInputExcel_1 --> tFileOutputDelimited_1

|

Subjob OK

|

tFileCopy_1

However,the converted csv file is saved in the same folder and not the new one.

The CSV file only copies the first column.

Instead of the csv,the excel file is renamed and saved in the new folder.

 

 


Accepted Solutions
Highlighted
Sixteen Stars
Sixteen Stars

Re: Converting Excel files to CSV and then rename file with date

In your tFileOutputDelimited, just set the File Name field like this:

"C:/Users/../" + 
TalendDate.formatDate("dd-MM-yyyy", TalendDate.getCurrentDate()) + "/" + 
TalendDate.formatDate("HHmm",TalendDate.getCurrentDate()) + "." + 
((String)globalMap.get("tFileList_1_CURRENT_FILEEXTENSION"))

Then in Advanced settings tab tick the option "Create directory if does not exist".

It should work.

 


TRF

View solution in original post


All Replies
Highlighted
Sixteen Stars
Sixteen Stars

Re: Converting Excel files to CSV and then rename file with date

Don't need tFileCopy for that. Just take care of the file path used in tFileOutputDelimited component. If it contains the right value for the "time" part, the output file will be placed into the desired folder.

TRF
Highlighted
Five Stars

Re: Converting Excel files to CSV and then rename file with date

I can see that i can name the file with desired time format when writing the filepath but how can I create a destination folder and name it based on the current date in tFileOutputDelimited?
Highlighted
Five Stars

Re: Converting Excel files to CSV and then rename file with date

Here are my  current settings : 

 

tFileOutputDelimited_1 Filepath : ((String)globalMap.get("tFileList_1_CURRENT_FILEPATH"))
tFileCopy destination folder : "C:/Users/../" + TalendDate.formatDate("dd-MM-yyyy", TalendDate.getCurrentDate())
tFileCopy destination file : TalendDate.formatDate("HHmm",TalendDate.getCurrentDate()) + "." + ((String)globalMap.get("tFileList_1_CURRENT_FILEEXTENSION"))
Highlighted
Sixteen Stars
Sixteen Stars

Re: Converting Excel files to CSV and then rename file with date

In your tFileOutputDelimited, just set the File Name field like this:

"C:/Users/../" + 
TalendDate.formatDate("dd-MM-yyyy", TalendDate.getCurrentDate()) + "/" + 
TalendDate.formatDate("HHmm",TalendDate.getCurrentDate()) + "." + 
((String)globalMap.get("tFileList_1_CURRENT_FILEEXTENSION"))

Then in Advanced settings tab tick the option "Create directory if does not exist".

It should work.

 


TRF

View solution in original post

Highlighted
Five Stars

Re: Converting Excel files to CSV and then rename file with date

Thank You! This resolved my initial problem.

My excel file is indeed converted into csv which is then renamed so your code worked! However, the csv file is empty and i get this exception : jxl.read.biff.BiffException: Unable to recognize OLE stream

at jxl.read.biff.CompoundFile.<init>(CompoundFile.java:116)
at jxl.read.biff.File.<init>(File.java:127)
at jxl.Workbook.getWorkbook(Workbook.java:268)
at local_project.test_0_1.Test.tFileList_1Process(Test.java:728)
at local_project.test_0_1.Test.runJobInTOS(Test.java:1301)
at local_project.test_0_1.Test.main(Test.java:1151)

Highlighted
Five Stars

Re: Converting Excel files to CSV and then rename file with date

I do have the "read excel2007 format (xlsx)" option unchecked.When i check it i only see the first column in my csv file

 

Highlighted
Sixteen Stars
Sixteen Stars

Re: Converting Excel files to CSV and then rename file with date

Share your job design with schema and any relevant information.
Also you should open a new subject.

TRF

2019 GARTNER MAGIC QUADRANT FOR DATA INTEGRATION TOOL

Talend named a Leader.

Get your copy

OPEN STUDIO FOR DATA INTEGRATION

Kickstart your first data integration and ETL projects.

Download now

Migrate Data from one Database to another with one Job using the Dynamic Schema

Find out how to migrate from one database to another using the Dynamic schema

Blog

Best Practices for Using Context Variables with Talend – Part 3

Read about some useful Context Variable ideas

Blog

Best Practices for Using Context Variables with Talend – Part 4

Pick up some tips and tricks with Context Variables

Blog