Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc,microsoft.public.excel.programming
|
|||
|
|||
Load a CSV into an empty, formatted XLS?
I have an empty, formatted spreadsheet, that I created by writing a few rows
into it from my COBOL program, field by field, then deleting all of the rows. The process of creating the entire XLS or XLSX runs much too slow (100 records per minute or less), and uses memory up, so can never allow me to finish converting some of my large CSV files into formatted XLS (Excel 2003) or XLSX (Excel 2007) files. Someone told me to "turn off continual re-calculating". How? I wonder if I can somehow import my large .CSV file into my .XLS empty formatted file? I cannot find a way. Any help would be appreciated. TIA, Paul |
#2
Posted to microsoft.public.excel.misc,microsoft.public.excel.programming
|
|||
|
|||
Load a CSV into an empty, formatted XLS?
First question is how big are the CSV files, in terms of rows and columns.
Since Excel has native built in ability to import CSV files, I presume you either have too many rows or too many columns. Hopefully not both. "Paul H" wrote: I have an empty, formatted spreadsheet, that I created by writing a few rows into it from my COBOL program, field by field, then deleting all of the rows. The process of creating the entire XLS or XLSX runs much too slow (100 records per minute or less), and uses memory up, so can never allow me to finish converting some of my large CSV files into formatted XLS (Excel 2003) or XLSX (Excel 2007) files. Someone told me to "turn off continual re-calculating". How? I wonder if I can somehow import my large .CSV file into my .XLS empty formatted file? I cannot find a way. Any help would be appreciated. TIA, Paul |
#3
Posted to microsoft.public.excel.misc,microsoft.public.excel.programming
|
|||
|
|||
Load a CSV into an empty, formatted XLS?
Oh, I forgot - The thing the guy was talking about was auto calculation. Set
it to Manual during the file import, set back to automatic later. It is under Tools - Options - and is found on the [Calculation] tab. "Paul H" wrote: I have an empty, formatted spreadsheet, that I created by writing a few rows into it from my COBOL program, field by field, then deleting all of the rows. The process of creating the entire XLS or XLSX runs much too slow (100 records per minute or less), and uses memory up, so can never allow me to finish converting some of my large CSV files into formatted XLS (Excel 2003) or XLSX (Excel 2007) files. Someone told me to "turn off continual re-calculating". How? I wonder if I can somehow import my large .CSV file into my .XLS empty formatted file? I cannot find a way. Any help would be appreciated. TIA, Paul |
#4
Posted to microsoft.public.excel.misc,microsoft.public.excel.programming
|
|||
|
|||
Load a CSV into an empty, formatted XLS?
I have many .CSV files. The one I've been testing with has 5004 rows and 41
columns. Others have more rows, but few have this many columns. When I import the .CSV, it is almost instant, but does not have columns formatted correctly. I have text columns of various widths, date columns, percent columns, numeric columns of various types, etc. So my COBOL programs formats each column correctly. My empty .XLS file has these formatting characteristics. So if I can import my .CVS into the empty .XLS file it should have the columns like I want them. I tried starting my program, stopping it so I could turn off recalc, then resumed the process - it stayed very slow. Importing my .CSV would appear to be my only option. I hope it can be accomplished. "JLatham" wrote in message ... First question is how big are the CSV files, in terms of rows and columns. Since Excel has native built in ability to import CSV files, I presume you either have too many rows or too many columns. Hopefully not both. "Paul H" wrote: I have an empty, formatted spreadsheet, that I created by writing a few rows into it from my COBOL program, field by field, then deleting all of the rows. The process of creating the entire XLS or XLSX runs much too slow (100 records per minute or less), and uses memory up, so can never allow me to finish converting some of my large CSV files into formatted XLS (Excel 2003) or XLSX (Excel 2007) files. Someone told me to "turn off continual re-calculating". How? I wonder if I can somehow import my large .CSV file into my .XLS empty formatted file? I cannot find a way. Any help would be appreciated. TIA, Paul |
#5
Posted to microsoft.public.excel.misc,microsoft.public.excel.programming
|
|||
|
|||
Load a CSV into an empty, formatted XLS?
turn off screen updating too
Application.ScreenUpdating=False "JLatham" wrote in message ... Oh, I forgot - The thing the guy was talking about was auto calculation. Set it to Manual during the file import, set back to automatic later. It is under Tools - Options - and is found on the [Calculation] tab. "Paul H" wrote: I have an empty, formatted spreadsheet, that I created by writing a few rows into it from my COBOL program, field by field, then deleting all of the rows. The process of creating the entire XLS or XLSX runs much too slow (100 records per minute or less), and uses memory up, so can never allow me to finish converting some of my large CSV files into formatted XLS (Excel 2003) or XLSX (Excel 2007) files. Someone told me to "turn off continual re-calculating". How? I wonder if I can somehow import my large .CSV file into my .XLS empty formatted file? I cannot find a way. Any help would be appreciated. TIA, Paul |
#6
Posted to microsoft.public.excel.misc,microsoft.public.excel.programming
|
|||
|
|||
Load a CSV into an empty, formatted XLS?
If you rename the .csv file to .txt, you may be able to import the file quicker
by setting each field the way you want. Then you could format the numeric fields as percentages or whatever you needed. If you have fields that have implicit decimals, you could import them as Generals and then put a factor of 10 (10, 100, 1000, ...) in an empty cell. Edit|copy, edit|paste special|Values and divide (all in code) to convert that field. Paul H wrote: I have an empty, formatted spreadsheet, that I created by writing a few rows into it from my COBOL program, field by field, then deleting all of the rows. The process of creating the entire XLS or XLSX runs much too slow (100 records per minute or less), and uses memory up, so can never allow me to finish converting some of my large CSV files into formatted XLS (Excel 2003) or XLSX (Excel 2007) files. Someone told me to "turn off continual re-calculating". How? I wonder if I can somehow import my large .CSV file into my .XLS empty formatted file? I cannot find a way. Any help would be appreciated. TIA, Paul -- Dave Peterson |
#7
Posted to microsoft.public.excel.misc,microsoft.public.excel.programming
|
|||
|
|||
Load a CSV into an empty, formatted XLS?
Then you don't have need of either of my special purpose CSV importing tools.
Look at what Dave Peterson has to say later on in this thread. If that is not enough, you could go through a one-time process of recording a new macro into your Personal.xls workbook while you go through the formatting of a workbook with imported data. If your formatting can be applied per column then you should be able to use that recorded macro to format your workbook(s) after importing the csv/txt file in the future. "Paul H" wrote: I have many .CSV files. The one I've been testing with has 5004 rows and 41 columns. Others have more rows, but few have this many columns. When I import the .CSV, it is almost instant, but does not have columns formatted correctly. I have text columns of various widths, date columns, percent columns, numeric columns of various types, etc. So my COBOL programs formats each column correctly. My empty .XLS file has these formatting characteristics. So if I can import my .CVS into the empty .XLS file it should have the columns like I want them. I tried starting my program, stopping it so I could turn off recalc, then resumed the process - it stayed very slow. Importing my .CSV would appear to be my only option. I hope it can be accomplished. "JLatham" wrote in message ... First question is how big are the CSV files, in terms of rows and columns. Since Excel has native built in ability to import CSV files, I presume you either have too many rows or too many columns. Hopefully not both. "Paul H" wrote: I have an empty, formatted spreadsheet, that I created by writing a few rows into it from my COBOL program, field by field, then deleting all of the rows. The process of creating the entire XLS or XLSX runs much too slow (100 records per minute or less), and uses memory up, so can never allow me to finish converting some of my large CSV files into formatted XLS (Excel 2003) or XLSX (Excel 2007) files. Someone told me to "turn off continual re-calculating". How? I wonder if I can somehow import my large .CSV file into my .XLS empty formatted file? I cannot find a way. Any help would be appreciated. TIA, Paul |
#8
Posted to microsoft.public.excel.misc,microsoft.public.excel.programming
|
|||
|
|||
Load a CSV into an empty, formatted XLS?
Thanks Dave,
I used your "import text" method and it does succeed in importing my .CSV file into my empty .XLS file, in about 2 seconds. That is, after I manually: 1. Open empty XLS file. 2. Data, import external data, import data. 3. "Select Data Source" screen comes up. 4. Type in my xxxx.TXT file name. 5. Import wizard step 1 - select delimited, then next. 6. Import wizard step 2 - select only Comma, then finish. 7. Import data to existing worksheet. 8. It imports the entire file in about 2 seconds, formatted correctly, with columns as described in my empty .XLS file. Now I need to figure out how to accomplish these steps automatically by my COBOL program. Thanks again, Paul ================================================== ======= "Dave Peterson" wrote in message ... If you rename the .csv file to .txt, you may be able to import the file quicker by setting each field the way you want. Then you could format the numeric fields as percentages or whatever you needed. If you have fields that have implicit decimals, you could import them as Generals and then put a factor of 10 (10, 100, 1000, ...) in an empty cell. Edit|copy, edit|paste special|Values and divide (all in code) to convert that field. ================================================== ======= Paul H wrote: I have an empty, formatted spreadsheet, that I created by writing a few rows into it from my COBOL program, field by field, then deleting all of the rows. The process of creating the entire XLS or XLSX runs much too slow (100 records per minute or less), and uses memory up, so can never allow me to finish converting some of my large CSV files into formatted XLS (Excel 2003) or XLSX (Excel 2007) files. Someone told me to "turn off continual re-calculating". How? I wonder if I can somehow import my large .CSV file into my .XLS empty formatted file? I cannot find a way. Any help would be appreciated. TIA, Paul -- Dave Peterson |
#9
Posted to microsoft.public.excel.misc,microsoft.public.excel.programming
|
|||
|
|||
Load a CSV into an empty, formatted XLS?
Thanks,"JLatham"
What are your "special purpose CSV importing tools"? Dave's method works, but I still need to solve the problem of automating it. Paul "JLatham" wrote in message ... Then you don't have need of either of my special purpose CSV importing tools. Look at what Dave Peterson has to say later on in this thread. If that is not enough, you could go through a one-time process of recording a new macro into your Personal.xls workbook while you go through the formatting of a workbook with imported data. If your formatting can be applied per column then you should be able to use that recorded macro to format your workbook(s) after importing the csv/txt file in the future. "Paul H" wrote: I have many .CSV files. The one I've been testing with has 5004 rows and 41 columns. Others have more rows, but few have this many columns. When I import the .CSV, it is almost instant, but does not have columns formatted correctly. I have text columns of various widths, date columns, percent columns, numeric columns of various types, etc. So my COBOL programs formats each column correctly. My empty .XLS file has these formatting characteristics. So if I can import my .CVS into the empty .XLS file it should have the columns like I want them. I tried starting my program, stopping it so I could turn off recalc, then resumed the process - it stayed very slow. Importing my .CSV would appear to be my only option. I hope it can be accomplished. "JLatham" wrote in message ... First question is how big are the CSV files, in terms of rows and columns. Since Excel has native built in ability to import CSV files, I presume you either have too many rows or too many columns. Hopefully not both. "Paul H" wrote: I have an empty, formatted spreadsheet, that I created by writing a few rows into it from my COBOL program, field by field, then deleting all of the rows. The process of creating the entire XLS or XLSX runs much too slow (100 records per minute or less), and uses memory up, so can never allow me to finish converting some of my large CSV files into formatted XLS (Excel 2003) or XLSX (Excel 2007) files. Someone told me to "turn off continual re-calculating". How? I wonder if I can somehow import my large .CSV file into my .XLS empty formatted file? I cannot find a way. Any help would be appreciated. TIA, Paul |
#10
Posted to microsoft.public.excel.misc,microsoft.public.excel.programming
|
|||
|
|||
Load a CSV into an empty, formatted XLS?
Maybe you could drop the requirement that the COBOL program do it.
Set up a workbook with two sheets (instructions for the user and the actual data). Record a macro that does all the work in the second sheet. The plop a button from the Forms toolbar onto the instruction sheet that calls that macro. You only have to rename the worksheet if you're doing File|Open (or the equivalent in code). If you use the import external data stuff, you can leave it named .csv. Paul H wrote: Thanks Dave, I used your "import text" method and it does succeed in importing my .CSV file into my empty .XLS file, in about 2 seconds. That is, after I manually: 1. Open empty XLS file. 2. Data, import external data, import data. 3. "Select Data Source" screen comes up. 4. Type in my xxxx.TXT file name. 5. Import wizard step 1 - select delimited, then next. 6. Import wizard step 2 - select only Comma, then finish. 7. Import data to existing worksheet. 8. It imports the entire file in about 2 seconds, formatted correctly, with columns as described in my empty .XLS file. Now I need to figure out how to accomplish these steps automatically by my COBOL program. Thanks again, Paul ================================================== ======= "Dave Peterson" wrote in message ... If you rename the .csv file to .txt, you may be able to import the file quicker by setting each field the way you want. Then you could format the numeric fields as percentages or whatever you needed. If you have fields that have implicit decimals, you could import them as Generals and then put a factor of 10 (10, 100, 1000, ...) in an empty cell. Edit|copy, edit|paste special|Values and divide (all in code) to convert that field. ================================================== ======= Paul H wrote: I have an empty, formatted spreadsheet, that I created by writing a few rows into it from my COBOL program, field by field, then deleting all of the rows. The process of creating the entire XLS or XLSX runs much too slow (100 records per minute or less), and uses memory up, so can never allow me to finish converting some of my large CSV files into formatted XLS (Excel 2003) or XLSX (Excel 2007) files. Someone told me to "turn off continual re-calculating". How? I wonder if I can somehow import my large .CSV file into my .XLS empty formatted file? I cannot find a way. Any help would be appreciated. TIA, Paul -- Dave Peterson -- Dave Peterson |
#11
Posted to microsoft.public.excel.misc,microsoft.public.excel.programming
|
|||
|
|||
Load a CSV into an empty, formatted XLS?
================================================== ======= I need the COBOL program to properly format the data. Multiple end-users will use this, sometimes daily, with data that changes daily, so I have been requested to make it completely automatic. Another subsequent program shows them the choices of reports and automatically starts the one they select. ================================================== ======= "Dave Peterson" wrote in message ... Maybe you could drop the requirement that the COBOL program do it. Set up a workbook with two sheets (instructions for the user and the actual data). Record a macro that does all the work in the second sheet. The plop a button from the Forms toolbar onto the instruction sheet that calls that macro. You only have to rename the worksheet if you're doing File|Open (or the equivalent in code). If you use the import external data stuff, you can leave it named .csv. ================================================== ======= Paul H wrote: Thanks Dave, I used your "import text" method and it does succeed in importing my .CSV file into my empty .XLS file, in about 2 seconds. That is, after I manually: 1. Open empty XLS file. 2. Data, import external data, import data. 3. "Select Data Source" screen comes up. 4. Type in my xxxx.TXT file name. 5. Import wizard step 1 - select delimited, then next. 6. Import wizard step 2 - select only Comma, then finish. 7. Import data to existing worksheet. 8. It imports the entire file in about 2 seconds, formatted correctly, with columns as described in my empty .XLS file. Now I need to figure out how to accomplish these steps automatically by my COBOL program. Thanks again, Paul ================================================== ======= "Dave Peterson" wrote in message ... If you rename the .csv file to .txt, you may be able to import the file quicker by setting each field the way you want. Then you could format the numeric fields as percentages or whatever you needed. If you have fields that have implicit decimals, you could import them as Generals and then put a factor of 10 (10, 100, 1000, ...) in an empty cell. Edit|copy, edit|paste special|Values and divide (all in code) to convert that field. ================================================== ======= Paul H wrote: I have an empty, formatted spreadsheet, that I created by writing a few rows into it from my COBOL program, field by field, then deleting all of the rows. The process of creating the entire XLS or XLSX runs much too slow (100 records per minute or less), and uses memory up, so can never allow me to finish converting some of my large CSV files into formatted XLS (Excel 2003) or XLSX (Excel 2007) files. Someone told me to "turn off continual re-calculating". How? I wonder if I can somehow import my large .CSV file into my .XLS empty formatted file? I cannot find a way. Any help would be appreciated. TIA, Paul -- Dave Peterson -- Dave Peterson |
#12
Posted to microsoft.public.excel.misc,microsoft.public.excel.programming
|
|||
|
|||
Load a CSV into an empty, formatted XLS?
It's too difficult to push a button?
If that's true, then name your macro Auto_Open. It'll run the first time someone opens the workbook. You could plop the date into a cell in a hidden sheet so that it only runs once. And add a save at the end. If that doesn't work, good luck with the automation. Paul H wrote: ================================================== ======= I need the COBOL program to properly format the data. Multiple end-users will use this, sometimes daily, with data that changes daily, so I have been requested to make it completely automatic. Another subsequent program shows them the choices of reports and automatically starts the one they select. ================================================== ======= "Dave Peterson" wrote in message ... Maybe you could drop the requirement that the COBOL program do it. Set up a workbook with two sheets (instructions for the user and the actual data). Record a macro that does all the work in the second sheet. The plop a button from the Forms toolbar onto the instruction sheet that calls that macro. You only have to rename the worksheet if you're doing File|Open (or the equivalent in code). If you use the import external data stuff, you can leave it named .csv. ================================================== ======= Paul H wrote: Thanks Dave, I used your "import text" method and it does succeed in importing my .CSV file into my empty .XLS file, in about 2 seconds. That is, after I manually: 1. Open empty XLS file. 2. Data, import external data, import data. 3. "Select Data Source" screen comes up. 4. Type in my xxxx.TXT file name. 5. Import wizard step 1 - select delimited, then next. 6. Import wizard step 2 - select only Comma, then finish. 7. Import data to existing worksheet. 8. It imports the entire file in about 2 seconds, formatted correctly, with columns as described in my empty .XLS file. Now I need to figure out how to accomplish these steps automatically by my COBOL program. Thanks again, Paul ================================================== ======= "Dave Peterson" wrote in message ... If you rename the .csv file to .txt, you may be able to import the file quicker by setting each field the way you want. Then you could format the numeric fields as percentages or whatever you needed. If you have fields that have implicit decimals, you could import them as Generals and then put a factor of 10 (10, 100, 1000, ...) in an empty cell. Edit|copy, edit|paste special|Values and divide (all in code) to convert that field. ================================================== ======= Paul H wrote: I have an empty, formatted spreadsheet, that I created by writing a few rows into it from my COBOL program, field by field, then deleting all of the rows. The process of creating the entire XLS or XLSX runs much too slow (100 records per minute or less), and uses memory up, so can never allow me to finish converting some of my large CSV files into formatted XLS (Excel 2003) or XLSX (Excel 2007) files. Someone told me to "turn off continual re-calculating". How? I wonder if I can somehow import my large .CSV file into my .XLS empty formatted file? I cannot find a way. Any help would be appreciated. TIA, Paul -- Dave Peterson -- Dave Peterson -- Dave Peterson |
#13
Posted to microsoft.public.excel.misc,microsoft.public.excel.programming
|
|||
|
|||
Load a CSV into an empty, formatted XLS?
================================================== ======= How do I record a macro? Do you mean I can create a macro that will do my steps 1 thru 8, below? ================================================== ======= "Dave Peterson" wrote in message ... It's too difficult to push a button? If that's true, then name your macro Auto_Open. It'll run the first time someone opens the workbook. You could plop the date into a cell in a hidden sheet so that it only runs once. And add a save at the end. If that doesn't work, good luck with the automation. Paul H wrote: ================================================== ======= I need the COBOL program to properly format the data. Multiple end-users will use this, sometimes daily, with data that changes daily, so I have been requested to make it completely automatic. Another subsequent program shows them the choices of reports and automatically starts the one they select. ================================================== ======= "Dave Peterson" wrote in message ... Maybe you could drop the requirement that the COBOL program do it. Set up a workbook with two sheets (instructions for the user and the actual data). Record a macro that does all the work in the second sheet. The plop a button from the Forms toolbar onto the instruction sheet that calls that macro. You only have to rename the worksheet if you're doing File|Open (or the equivalent in code). If you use the import external data stuff, you can leave it named .csv. ================================================== ======= Paul H wrote: Thanks Dave, I used your "import text" method and it does succeed in importing my .CSV file into my empty .XLS file, in about 2 seconds. That is, after I manually: 1. Open empty XLS file. 2. Data, import external data, import data. 3. "Select Data Source" screen comes up. 4. Type in my xxxx.TXT file name. 5. Import wizard step 1 - select delimited, then next. 6. Import wizard step 2 - select only Comma, then finish. 7. Import data to existing worksheet. 8. It imports the entire file in about 2 seconds, formatted correctly, with columns as described in my empty .XLS file. Now I need to figure out how to accomplish these steps automatically by my COBOL program. Thanks again, Paul ================================================== ======= "Dave Peterson" wrote in message ... If you rename the .csv file to .txt, you may be able to import the file quicker by setting each field the way you want. Then you could format the numeric fields as percentages or whatever you needed. If you have fields that have implicit decimals, you could import them as Generals and then put a factor of 10 (10, 100, 1000, ...) in an empty cell. Edit|copy, edit|paste special|Values and divide (all in code) to convert that field. ================================================== ======= Paul H wrote: I have an empty, formatted spreadsheet, that I created by writing a few rows into it from my COBOL program, field by field, then deleting all of the rows. The process of creating the entire XLS or XLSX runs much too slow (100 records per minute or less), and uses memory up, so can never allow me to finish converting some of my large CSV files into formatted XLS (Excel 2003) or XLSX (Excel 2007) files. Someone told me to "turn off continual re-calculating". How? I wonder if I can somehow import my large .CSV file into my .XLS empty formatted file? I cannot find a way. Any help would be appreciated. TIA, Paul -- Dave Peterson -- Dave Peterson -- Dave Peterson |
#14
Posted to microsoft.public.excel.misc,microsoft.public.excel.programming
|
|||
|
|||
Load a CSV into an empty, formatted XLS?
In Excel 2007 you will need to be able to see the [Developer] tab. If it is
not visible now, click the Office Button, then the [Excel Options] button near the lower right of the window that opens. In the "Popular" group, enable the "Show Developer tab in the ribbon" option. To record a macro, choose the Developer tab and click the "Record Macro" - give it a name and procede with the steps you wish to record. The "Record Macro" option will have changed to "Stop Recording". Click it when you have finished recording the steps you need to repeat later. "Paul H" wrote: ================================================== ======= How do I record a macro? Do you mean I can create a macro that will do my steps 1 thru 8, below? ================================================== ======= "Dave Peterson" wrote in message ... It's too difficult to push a button? If that's true, then name your macro Auto_Open. It'll run the first time someone opens the workbook. You could plop the date into a cell in a hidden sheet so that it only runs once. And add a save at the end. If that doesn't work, good luck with the automation. Paul H wrote: ================================================== ======= I need the COBOL program to properly format the data. Multiple end-users will use this, sometimes daily, with data that changes daily, so I have been requested to make it completely automatic. Another subsequent program shows them the choices of reports and automatically starts the one they select. ================================================== ======= "Dave Peterson" wrote in message ... Maybe you could drop the requirement that the COBOL program do it. Set up a workbook with two sheets (instructions for the user and the actual data). Record a macro that does all the work in the second sheet. The plop a button from the Forms toolbar onto the instruction sheet that calls that macro. You only have to rename the worksheet if you're doing File|Open (or the equivalent in code). If you use the import external data stuff, you can leave it named .csv. ================================================== ======= Paul H wrote: Thanks Dave, I used your "import text" method and it does succeed in importing my .CSV file into my empty .XLS file, in about 2 seconds. That is, after I manually: 1. Open empty XLS file. 2. Data, import external data, import data. 3. "Select Data Source" screen comes up. 4. Type in my xxxx.TXT file name. 5. Import wizard step 1 - select delimited, then next. 6. Import wizard step 2 - select only Comma, then finish. 7. Import data to existing worksheet. 8. It imports the entire file in about 2 seconds, formatted correctly, with columns as described in my empty .XLS file. Now I need to figure out how to accomplish these steps automatically by my COBOL program. Thanks again, Paul ================================================== ======= "Dave Peterson" wrote in message ... If you rename the .csv file to .txt, you may be able to import the file quicker by setting each field the way you want. Then you could format the numeric fields as percentages or whatever you needed. If you have fields that have implicit decimals, you could import them as Generals and then put a factor of 10 (10, 100, 1000, ...) in an empty cell. Edit|copy, edit|paste special|Values and divide (all in code) to convert that field. ================================================== ======= Paul H wrote: I have an empty, formatted spreadsheet, that I created by writing a few rows into it from my COBOL program, field by field, then deleting all of the rows. The process of creating the entire XLS or XLSX runs much too slow (100 records per minute or less), and uses memory up, so can never allow me to finish converting some of my large CSV files into formatted XLS (Excel 2003) or XLSX (Excel 2007) files. Someone told me to "turn off continual re-calculating". How? I wonder if I can somehow import my large .CSV file into my .XLS empty formatted file? I cannot find a way. Any help would be appreciated. TIA, Paul -- Dave Peterson -- Dave Peterson -- Dave Peterson |
#15
Posted to microsoft.public.excel.misc,microsoft.public.excel.programming
|
|||
|
|||
Load a CSV into an empty, formatted XLS?
I tried it using Excel 2003 and it works there also. I'll try the entire process tomorrow (Monday). Wish me luck, and thanks. Paul ================================================== ======= "JLatham" wrote in message ... In Excel 2007 you will need to be able to see the [Developer] tab. If it is not visible now, click the Office Button, then the [Excel Options] button near the lower right of the window that opens. In the "Popular" group, enable the "Show Developer tab in the ribbon" option. To record a macro, choose the Developer tab and click the "Record Macro" - give it a name and procede with the steps you wish to record. The "Record Macro" option will have changed to "Stop Recording". Click it when you have finished recording the steps you need to repeat later. "Paul H" wrote: ================================================== ======= How do I record a macro? Do you mean I can create a macro that will do my steps 1 thru 8, below? ================================================== ======= "Dave Peterson" wrote in message ... It's too difficult to push a button? If that's true, then name your macro Auto_Open. It'll run the first time someone opens the workbook. You could plop the date into a cell in a hidden sheet so that it only runs once. And add a save at the end. If that doesn't work, good luck with the automation. Paul H wrote: ================================================== ======= I need the COBOL program to properly format the data. Multiple end-users will use this, sometimes daily, with data that changes daily, so I have been requested to make it completely automatic. Another subsequent program shows them the choices of reports and automatically starts the one they select. ================================================== ======= "Dave Peterson" wrote in message ... Maybe you could drop the requirement that the COBOL program do it. Set up a workbook with two sheets (instructions for the user and the actual data). Record a macro that does all the work in the second sheet. The plop a button from the Forms toolbar onto the instruction sheet that calls that macro. You only have to rename the worksheet if you're doing File|Open (or the equivalent in code). If you use the import external data stuff, you can leave it named .csv. ================================================== ======= Paul H wrote: Thanks Dave, I used your "import text" method and it does succeed in importing my .CSV file into my empty .XLS file, in about 2 seconds. That is, after I manually: 1. Open empty XLS file. 2. Data, import external data, import data. 3. "Select Data Source" screen comes up. 4. Type in my xxxx.TXT file name. 5. Import wizard step 1 - select delimited, then next. 6. Import wizard step 2 - select only Comma, then finish. 7. Import data to existing worksheet. 8. It imports the entire file in about 2 seconds, formatted correctly, with columns as described in my empty .XLS file. Now I need to figure out how to accomplish these steps automatically by my COBOL program. Thanks again, Paul ================================================== ======= "Dave Peterson" wrote in message ... If you rename the .csv file to .txt, you may be able to import the file quicker by setting each field the way you want. Then you could format the numeric fields as percentages or whatever you needed. If you have fields that have implicit decimals, you could import them as Generals and then put a factor of 10 (10, 100, 1000, ...) in an empty cell. Edit|copy, edit|paste special|Values and divide (all in code) to convert that field. ================================================== ======= Paul H wrote: I have an empty, formatted spreadsheet, that I created by writing a few rows into it from my COBOL program, field by field, then deleting all of the rows. The process of creating the entire XLS or XLSX runs much too slow (100 records per minute or less), and uses memory up, so can never allow me to finish converting some of my large CSV files into formatted XLS (Excel 2003) or XLSX (Excel 2007) files. Someone told me to "turn off continual re-calculating". How? I wonder if I can somehow import my large .CSV file into my .XLS empty formatted file? I cannot find a way. Any help would be appreciated. TIA, Paul -- Dave Peterson -- Dave Peterson -- Dave Peterson |
#16
Posted to microsoft.public.excel.misc,microsoft.public.excel.programming
|
|||
|
|||
Load a CSV into an empty, formatted XLS?
the macro recorder will show you the very basic VBA code. you'll want to
tidy up :) but its a great way to start "Paul H" wrote in message ... I tried it using Excel 2003 and it works there also. I'll try the entire process tomorrow (Monday). Wish me luck, and thanks. Paul ================================================== ======= "JLatham" wrote in message ... In Excel 2007 you will need to be able to see the [Developer] tab. If it is not visible now, click the Office Button, then the [Excel Options] button near the lower right of the window that opens. In the "Popular" group, enable the "Show Developer tab in the ribbon" option. To record a macro, choose the Developer tab and click the "Record Macro" - give it a name and procede with the steps you wish to record. The "Record Macro" option will have changed to "Stop Recording". Click it when you have finished recording the steps you need to repeat later. "Paul H" wrote: ================================================== ======= How do I record a macro? Do you mean I can create a macro that will do my steps 1 thru 8, below? ================================================== ======= "Dave Peterson" wrote in message ... It's too difficult to push a button? If that's true, then name your macro Auto_Open. It'll run the first time someone opens the workbook. You could plop the date into a cell in a hidden sheet so that it only runs once. And add a save at the end. If that doesn't work, good luck with the automation. Paul H wrote: ================================================== ======= I need the COBOL program to properly format the data. Multiple end-users will use this, sometimes daily, with data that changes daily, so I have been requested to make it completely automatic. Another subsequent program shows them the choices of reports and automatically starts the one they select. ================================================== ======= "Dave Peterson" wrote in message ... Maybe you could drop the requirement that the COBOL program do it. Set up a workbook with two sheets (instructions for the user and the actual data). Record a macro that does all the work in the second sheet. The plop a button from the Forms toolbar onto the instruction sheet that calls that macro. You only have to rename the worksheet if you're doing File|Open (or the equivalent in code). If you use the import external data stuff, you can leave it named .csv. ================================================== ======= Paul H wrote: Thanks Dave, I used your "import text" method and it does succeed in importing my .CSV file into my empty .XLS file, in about 2 seconds. That is, after I manually: 1. Open empty XLS file. 2. Data, import external data, import data. 3. "Select Data Source" screen comes up. 4. Type in my xxxx.TXT file name. 5. Import wizard step 1 - select delimited, then next. 6. Import wizard step 2 - select only Comma, then finish. 7. Import data to existing worksheet. 8. It imports the entire file in about 2 seconds, formatted correctly, with columns as described in my empty .XLS file. Now I need to figure out how to accomplish these steps automatically by my COBOL program. Thanks again, Paul ================================================== ======= "Dave Peterson" wrote in message ... If you rename the .csv file to .txt, you may be able to import the file quicker by setting each field the way you want. Then you could format the numeric fields as percentages or whatever you needed. If you have fields that have implicit decimals, you could import them as Generals and then put a factor of 10 (10, 100, 1000, ...) in an empty cell. Edit|copy, edit|paste special|Values and divide (all in code) to convert that field. ================================================== ======= Paul H wrote: I have an empty, formatted spreadsheet, that I created by writing a few rows into it from my COBOL program, field by field, then deleting all of the rows. The process of creating the entire XLS or XLSX runs much too slow (100 records per minute or less), and uses memory up, so can never allow me to finish converting some of my large CSV files into formatted XLS (Excel 2003) or XLSX (Excel 2007) files. Someone told me to "turn off continual re-calculating". How? I wonder if I can somehow import my large .CSV file into my .XLS empty formatted file? I cannot find a way. Any help would be appreciated. TIA, Paul -- Dave Peterson -- Dave Peterson -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How can I display symbols in phone # formatted cell when empty? | Excel Worksheet Functions | |||
now get message "disk is not formatted or formatted for Mac" | Excel Discussion (Misc queries) | |||
why a reference to an empty cell is not considered empty | Excel Discussion (Misc queries) | |||
Excel - Autom. Filter "Empty / Non Empty cells" should come first | Excel Discussion (Misc queries) | |||
Converting 'General' formatted cells to Text formatted cell using. | Excel Worksheet Functions |