How to get EXCEL from CSV-file automated, without "manuell Import data" ?

asdf13
asdf13 used Ask the Experts™
on
How to get EXCEL from CSV-file automated, without "manuell Import data" ?
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Fabrice LambertConsulting
Distinguished Expert 2017

Commented:
Hi,

Can you be more precise about what you want to do ?

Excel is perfectly able to read CSV files, just double click on the file icon from your file browser (but I assume this isn't your desired results).
Else, did you look at the code produced by the macro recorder ?

Author

Commented:
If i open csv file with XLS, i get a layout like attached file Shows  (comma separated, not in XLS colums )
CSV-open-with-EXCEL.PNG
Fabrice LambertConsulting
Distinguished Expert 2017

Commented:
How do you open it ?
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Author

Commented:
open with EXCEL ....
Save CSV file under EXCEL did not put CSV data in columns (no effect, same as before)  

If i open new EXCEL and do "Import data from Text" from CSV file , CSV-data will be put in EXCEl columns, as wanted.
But i want to avoid these manuell steps . ..
Bill PrewIT / Software Engineering Consultant
Top Expert 2016

Commented:
Please upload an example of the actual CSV file that is not opening properly in Excel.


»bp

Author

Commented:
example CSV attached : CSV_EXAMPL2.CSV
CSV_EXAMPL2.CSV
IT / Software Engineering Consultant
Top Expert 2016
Commented:
The problem with that file is all the extra repeat double quotes.  I cleaned them up just removing the duplicates (see second file below) and it opens fine in Excel, parsing into columns.  You need to correct the source of the file, or write a small script to remove the extra double quotes before opening in Excel.

***** ORIGINAL FILE *****
"PARENT DIRECTORY,""OBJECT NAME"",""ALLOCATED SIZE (BYTES)"",""OBJECT OWNER"",""DATETIME OBJECT LAST USED"""
"/QOpenSys/QIBM/ProdData/C++Compiler/bin/n,""QPZA_QSI678235770WDS0007R03M00510100520000"",41943040            ,""QSYS      "",""2018-11-17-19.24.23.000000"""
"/QOpenSys/QIBM/ProdData/C++Compiler/bin/n,""xlCentry"",41943040            ,""QSYS      "",""2018-11-17-19.24.23.000000"""
"/QOpenSys/QIBM/ProdData/C++Compiler/bin/n_1,""xlCentry"",25165824            ,""QSYS      "",""2018-11-17-19.24.23.000000"""
"/QOpenSys/QIBM/ProdData/C++Compiler/bin/n_2,""xlCentry"",27262976            ,""QSYS      "",""2018-11-17-19.24.23.000000"""
"/QOpenSys/QIBM/ProdData/InfoprintServer/Transforms/pcl2afp,""pcl6"",7864320             ,""QDFTOWN   "",""2018-06-13-07.50.06.000000"""
"/QOpenSys/QIBM/ProdData/InfoprintServer/Transforms/ps2afp/Resource/CIDFont,""AdobeMingStd-Light"",13631488            ,""QDFTOWN   "",""2018-06-13-07.50.06.000000"""
"/QOpenSys/QIBM/ProdData/InfoprintServer/Transforms/ps2afp/Resource/CIDFont,""AdobeMyungjoStd-Medium"",11534336            ,""QDFTOWN   "",""2018-06-13-07.50.06.000000"""
"/QOpenSys/QIBM/ProdData/InfoprintServer/Transforms/ps2afp/Resource/CIDFont,""AdobeSongStd-Light"",19922944            ,""QDFTOWN   "",""2018-06-13-07.50.06.000000"""
"/QOpenSys/QIBM/ProdData/InfoprintServer/Transforms/ps2afp/Resource/CIDFont,""KozGoPro-Medium"",5767168             ,""QDFTOWN   "",""2018-06-13-07.50.06.000000"""
"/QOpenSys/QIBM/ProdData/InfoprintServer/Transforms/ps2afp/Resource/CIDFont,""KozMinProVI-Regular"",10485760            ,""QDFTOWN   "",""2018-06-13-07.50.06.000000"""

Open in new window

***** CORRECTED FILE *****
"PARENT DIRECTORY,"OBJECT NAME","ALLOCATED SIZE (BYTES)","OBJECT OWNER","DATETIME OBJECT LAST USED"
"/QOpenSys/QIBM/ProdData/C++Compiler/bin/n,"QPZA_QSI678235770WDS0007R03M00510100520000",41943040            ,"QSYS      ","2018-11-17-19.24.23.000000"
"/QOpenSys/QIBM/ProdData/C++Compiler/bin/n,"xlCentry",41943040            ,"QSYS      ","2018-11-17-19.24.23.000000"
"/QOpenSys/QIBM/ProdData/C++Compiler/bin/n_1,"xlCentry",25165824            ,"QSYS      ","2018-11-17-19.24.23.000000"
"/QOpenSys/QIBM/ProdData/C++Compiler/bin/n_2,"xlCentry",27262976            ,"QSYS      ","2018-11-17-19.24.23.000000"
"/QOpenSys/QIBM/ProdData/InfoprintServer/Transforms/pcl2afp,"pcl6",7864320             ,"QDFTOWN   ","2018-06-13-07.50.06.000000"
"/QOpenSys/QIBM/ProdData/InfoprintServer/Transforms/ps2afp/Resource/CIDFont,"AdobeMingStd-Light",13631488            ,"QDFTOWN   ","2018-06-13-07.50.06.000000"
"/QOpenSys/QIBM/ProdData/InfoprintServer/Transforms/ps2afp/Resource/CIDFont,"AdobeMyungjoStd-Medium",11534336            ,"QDFTOWN   ","2018-06-13-07.50.06.000000"
"/QOpenSys/QIBM/ProdData/InfoprintServer/Transforms/ps2afp/Resource/CIDFont,"AdobeSongStd-Light",19922944            ,"QDFTOWN   ","2018-06-13-07.50.06.000000"
"/QOpenSys/QIBM/ProdData/InfoprintServer/Transforms/ps2afp/Resource/CIDFont,"KozGoPro-Medium",5767168             ,"QDFTOWN   ","2018-06-13-07.50.06.000000"
"/QOpenSys/QIBM/ProdData/InfoprintServer/Transforms/ps2afp/Resource/CIDFont,"KozMinProVI-Regular",10485760            ,"QDFTOWN   ","2018-06-13-07.50.06.000000"

Open in new window


»bp

Author

Commented:
thanks both for supporting !

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial