Hi guys,
I have tousands of data in .SDF format. The file consists of rows and columns. I need to convert this file into xls format. For this I first converted this file into notepad format and then opened it with excel. The problem is that the original file does not have spaces between the characters and therefore excel opens the file in a single column only. As far as I know, for the excel to convert the file into as many columns and rows as the original file have, there should be spaces between the characters. Can anyone tell me an easy way of generating spaces between characters in notepad so that i do not have to insert 100 spaces in each row x 3000 rows manually.
Thanks in advance
Cevat
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
Excel can import files using fixed width columns. You just have to tell it where the columns are.
But you say there are 100 columns, so that could be a little tedious (but easier than anything you can do in Notepad++). It might be easier to do it in Excel. You could read it into a single column, then write formulas to extract it into columns. E.g. =mid(a1,1,4), that kind of thing. If all the columns are the same width you could write a clever formula that works out what to extract based on the column number, so you don't have to write 100 formulas.
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
There is an import wizzard in Excel with which you can simply set column ranges for text files to be converted to column cells. You will see a few text lines in a window and be able to set the column ranges manually. You can even save this layout as a template. This works a little easier than having to figure out all those numbers to put in many formula's.
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
Hi guys,
I have tousands of data in .SDF format. The file consists of rows and columns. I need to convert this file into xls format. For this I first converted this file into notepad format and then opened it with excel. The problem is that the original file does not have spaces between the characters and therefore excel opens the file in a single column only. As far as I know, for the excel to convert the file into as many columns and rows as the original file have, there should be spaces between the characters. Can anyone tell me an easy way of generating spaces between characters in notepad so that i do not have to insert 100 spaces in each row x 3000 rows manually.
Thanks in advance
Cevat
Here is a detailed description of the text import functionality available in Excel:
http://office.microsoft.com/training/training.aspx?AssetID=RC011801181033
Excel can import files using fixed width columns. You just have to tell it where the columns are.
But you say there are 100 columns, so that could be a little tedious (but easier than anything you can do in Notepad++). It might be easier to do it in Excel. You could read it into a single column, then write formulas to extract it into columns. E.g. =mid(a1,1,4), that kind of thing. If all the columns are the same width you could write a clever formula that works out what to extract based on the column number, so you don't have to write 100 formulas.
There is an import wizzard in Excel with which you can simply set column ranges for text files to be converted to column cells. You will see a few text lines in a window and be able to set the column ranges manually. You can even save this layout as a template. This works a little easier than having to figure out all those numbers to put in many formula's.