Import and merge many columns and many Excel files
Locked
Import and merge many columns and many Excel files
 
|
Hi Everyone, I need to import and merge data from 36 excel files with 366 columns and 1440 rows each but I’m not sure how to using syntax. Step 1. I need to merge all 366 variables into one variable, i.e. from below format in Excel Time Day1 Day2 … Day366 1 9 1 6 2 0 5 8 : : : : 1440 12 41 23 to this in SPSS Time File1 1 9 2 0 : : 1440 12 1 1 2 5 : : 1440 41 : Step 2. I need to combine all 36 files together into one file Time File1 File2 File3 … File36 1 9 2 0 : : 1440 12 1 1 2 5 : : 1440 41 Any help would be appreciated. Boreak This email is intended solely for the named addressee. |
|
|
Hi Boreak,
I had a similar task last year (thread 'import and merge multiple Excel files' - http://spssx-discussion.1045642.n5.nabble.com/import-and-merge-multiple-Excel-files-td5716664.html), and while the data structures are different, the same general process may help you here. I used the SPSSINC PROCESS FILES Python addon command. The PROCESS FILES command calls a syntax file that you specify and will run it over every file in your nominated directory. In my case my syntax file consisted of a GET DATA to import the Excel file and an ADD FILES to merge the newly imported file to the file created at the previous step. In your case I think you could use PROCESS FILES to call a syntax file that: 1. Imports the Excel file 2. Restructures the file to the long format 3. Merges (add variables) the newly restructured file to the previous file. Andy W's post in the thread above includes some links to the developer forum, including a complete example. Hopefully this will help get you started. Cheers, Kylie. From: SPSSX(r) Discussion [[hidden email]] on behalf of Boreak Silk [[hidden email]]
Sent: Thursday, 2 May 2013 1:59 PM To: [hidden email] Subject: Import and merge many columns and many Excel files Hi Everyone,
I need to import and merge data from 36 excel files with 366 columns and 1440 rows each but I’m not sure how to using syntax. Step 1. I need to merge all 366 variables into one variable, i.e. from below format in Excel
Time Day1 Day2 … Day366 1 9 1 6 2 0 5 8 : : : : 1440 12 41 23
to this in SPSS Time File1 1 9 2 0 : : 1440 12 1 1 2 5 : : 1440 41 :
Step 2. I need to combine all 36 files together into one file Time File1 File2 File3 … File36 1 9 2 0 : : 1440 12 1 1 2 5 : : 1440 41
Any help would be appreciated.
Boreak
This email is intended solely for the named addressee. |
Re: Import and merge many columns and many Excel files
|
|
In reply to this post by Boreak Silk
So, you need to read an excel file, restructure it and match it to the previous files. You need a macro because you are going to do the same thing for every file. I don’t have any skill with macros. For file 1 you have this. Get data …. * y is the variable whose values are recorded minute by minute every day varstocases make y from day1 to day366/index=day. sort cases by day time. Save outfile=<cumulant file name> * File 2 and on. Get data …. varstocases make y from day1 to day366/index=day. sort cases by day time. Match files file=<cumulant file name>/file=*/rename=(y=y2)/by time day. Save outfile=<cumulant file name> If you run spss with ‘have multiple data files open’ enabled, which I don’t do, you will have, I think, a slightly different structure due to needing to keep track of opening and closing datasets. If the file names of your 36 files are nicely structured, a macro would be frosting. But I can’t do it for you. Gene Maguin From: SPSSX(r) Discussion [mailto:[hidden email]] On Behalf Of Boreak Silk Hi Everyone, I need to import and merge data from 36 excel files with 366 columns and 1440 rows each but I’m not sure how to using syntax. Step 1. I need to merge all 366 variables into one variable, i.e. from below format in Excel Time Day1 Day2 … Day366 1 9 1 6 2 0 5 8 : : : : 1440 12 41 23 to this in SPSS Time File1 1 9 2 0 : : 1440 12 1 1 2 5 : : 1440 41 : Step 2. I need to combine all 36 files together into one file Time File1 File2 File3 … File36 1 9 2 0 : : 1440 12 1 1 2 5 : : 1440 41 Any help would be appreciated. Boreak This email is intended solely for the named addressee. |
Re: Import and merge many columns and many Excel files
|
|
In reply to this post by Kylie
Hi Kylie and Eugene, Thank for your advice. Cheers, Boreak From: Kylie Lange [mailto:[hidden email]] Hi Boreak, From: SPSSX(r) Discussion [[hidden email]] on behalf of Boreak Silk [[hidden email]] Hi Everyone, I need to import and merge data from 36 excel files with 366 columns and 1440 rows each but I’m not sure how to using syntax. Step 1. I need to merge all 366 variables into one variable, i.e. from below format in Excel Time Day1 Day2 … Day366 1 9 1 6 2 0 5 8 : : : : 1440 12 41 23 to this in SPSS Time File1 1 9 2 0 : : 1440 12 1 1 2 5 : : 1440 41 : Step 2. I need to combine all 36 files together into one file Time File1 File2 File3 … File36 1 9 2 0 : : 1440 12 1 1 2 5 : : 1440 41 Any help would be appreciated. Boreak This email is intended solely for the named addressee. |
| Free forum by Nabble | Edit this page |