Converting Excel files to CSV and then rename file with date

[Anonymous]

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.

[TRF]

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.

[Anonymous]

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?

[Anonymous]

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"))

[TRF · Accepted solution]

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.

[Anonymous]

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)

[Anonymous]

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

[TRF]

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