How do I reorganize a single list of data into a CSV in Excel?

Question

I have a long list of data that follows a very exact pattern. Reading it and interpreting it is simple; but, I would like to use a script through the list and create a spreadsheet so that it's easier to work with the data. Your help and scripting skills will be greatly appreciated.

The raw list looks like list:

http://mydomain.com/?1
2.23
1.06
0.45
http://mydomain.com/?2
0.36
1.25
0.21
2.36
http://mydomain.com/?3
0.60
2.30
1.30

I would like to read the list (from the last row up...?) and rewrite the data so that it looks like this (comma separated here... in excel, possible to just move to the prior row's last blank cell):

http://mydomain.com/?1, 2.23, 1.06, 0.45
http://mydomain.com/?2, 0.36, 1.25, 0.21, 2.36
http://mydomain.com/?3, 0.60, 2.30, 1.30

I'm happy to provide more details. I will use and award for any method that gives that final result. Thank you!

CinderBlock · Accepted Answer

If the list is in a text file, it would be easier to do that with a program than an excel macro, e.g., in C it would be:

char buffer[1000];
char outline[10000];
outline[0]=0;
while (gets(buffer)!=EOF) {
  buffer[strlen(buffer)]=0; /* eliminate newline char */
  if (buffer[0]=='h') {
     if (outline[0]) puts(outline);
     strcpy(outline,buffer);
  }
  else {
     strcat(outline,', ');
     strcat(outline, buffer);
  }
}
puts(outline);  


then you pipe the source file into the program, and it streams the output lines all concatenated to stdout.

Anonymous · Answer

File/Save As .../ Change the Sav as Type (on the bottom of the Save dialog) to CSV.  Save the file.  It'll be a CSV file.

If you're using Access as your database, don't even bother - just import the xls file.

Trending News

How do I reorganize a single list of data into a CSV in Excel?

2 Answers