Sign up to take part
Registered users can ask their own questions, contribute to discussions, and be part of the Community!
Registered users can ask their own questions, contribute to discussions, and be part of the Community!
Hi,
I have some issue when I'm reading Excel files stored in folder.
I import 1 file by month in my folder and I create a dataset to read all files and obtain one stacked table as result. For some of them (but not all), the format is not read like it's stored :
So, at the end, I have mixed of format in my column and I can't use parsing on prepare recipe.
Any idea to correct this ? (I would like avoid to modify manually Excel files)
Thanks in advance
The key in your post is "sometimes". This is a strong indication that your problem is on your Excel file. I am afraid that you really need to go back to your Excel file and fix your data. Most likely you have a date column which has a combination of dates stored as dates and dates stored as text. Use the techniques on the following link to fix your spreadsheet:
https://www.macroption.com/excel-reverse-date/
"I would like avoid to modify manually Excel files" => Then don't use Excel files, use CSV files which don't introduce a mixed format issue.
The key in your post is "sometimes". This is a strong indication that your problem is on your Excel file. I am afraid that you really need to go back to your Excel file and fix your data. Most likely you have a date column which has a combination of dates stored as dates and dates stored as text. Use the techniques on the following link to fix your spreadsheet:
https://www.macroption.com/excel-reverse-date/
"I would like avoid to modify manually Excel files" => Then don't use Excel files, use CSV files which don't introduce a mixed format issue.
Hi @Turribeach,
Thanks for your answer !
You're right, my problem is due to format storage in some Excel files : sometimes as date and sometimes as text.
I use technique described on the link you suggest me and it works well.
I made some tests on distinct storage, so I complete your answer if it could helps for other people :
The first solution is the best one to avoid a second step executed in DSS Engine (parse Date isn't compatible with SQL)
Regarding CSV file, I'm totally agreed with you : it's the best solution to avoid this issue. Unfortunately, these files are created by business team for their uses and not for mine ...
Thanks for the update, I am sure this is a very common issue with Excel. To be honest I wouldn't blame the users, in this day and age for Microsoft to not fix this automatically when the user pastes/inputs the data is almost criminal. But they still charge everyone their 365 subscription no matter what...