Solved

How to convert a CSV file into a fixed width file

Posted on 2014-12-10
13
377 Views
Last Modified: 2015-01-01
Hi all,

I'm trying to automate the conversion of a csv file into a fixed width file. I am proposing to use a MSaccess program which would have the file imported into it, carrying out a process and then producing the required file.

to start off with, I was wondering if this is possible?

Thanks
0
Comment
Question by:PipMic
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 4
  • 2
  • +1
13 Comments
 
LVL 74

Accepted Solution

by:
Jeffrey Coachman earned 300 total points
ID: 40491971
Typically this is done by creating an Import specification (by importing the file manually, then saving the import specification )

Have you tried that?


Note that you cant simply "Convert" a file to fixed width, ...the internal structure of the file may need to be modified...

It may also be helpful if you posted a sample of the file before and after conversion
0
 
LVL 29

Expert Comment

by:Olaf Doschke
ID: 40491973
Yes, totally possible.

Define an import table with fixed width char fields, import CSV, then export that table into a fixed width text file.

Bye, Olaf.
0
 
LVL 74

Assisted Solution

by:Jeffrey Coachman
Jeffrey Coachman earned 300 total points
ID: 40492004
I'll be clearer because there are also "saved Imports"

Import/Export
Text File
Browse to the file/location
Select: Import the Source...
Select "Fixed Width"
Adjust the width to align to what you need
...Click through all the other wizards steps, ...stopping at the last screen
Then click "Advanced", and save your import spec with a name.
(to avoid confusion, DO NOT click the box to "save import steps")
...this is NOT the same as the Import specification you just created...

Now you can use this import spec to import other "Similarly formatted" text files with code similar to this:
DoCmd.TransferText acImportFixed, "YourImportSpecName", "TargetTableName", "c:\YourFolder\Yourfile.csv"

JeffCoachman
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:PipMic
ID: 40492029
Thanks guys,

In the first instance I just wanted to know whether it could be done and as usual I perhaps have not explained myself well enough (apologies Coachman!) or probably not used the correct terminology.

An example type of record I am importing is as follows
123456,88888888,A N Other,,900.00

This is a record composed of 5 fields (the fourth in my example is blank but could be populated)

My aim is to import that file (with many similar records) into a program which would then create a file with records with a fixed length, say of 106 characters.

Therefore the data held in the original CSV file would  insert in specific locations of that long record.

So far, Is that still possible? and should I explain further?

Please, I hope I do not offend if I am coming across badly, it is not my intention.

Thanks
0
 
LVL 74

Assisted Solution

by:Jeffrey Coachman
Jeffrey Coachman earned 300 total points
ID: 40492049
Yes,

Just run through the wizard and set the width.
...
Then save the Import spec.
Then you can use this same import spec in code for all future  imports of similar files
DoCmd.TransferText acImportFixed, "YourImportSpecName", "TargetTableName", "c:\YourFolder\Yourfile.csv"

Open in new window

0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 40492052
< (apologies Coachman!) >
No need to apologize, ...I consider us old friends now...
;-)

Jeff
0
 
LVL 37

Assisted Solution

by:PatHartman
PatHartman earned 100 total points
ID: 40492070
You can link to the .csv file.  There is no need to import it unless you need to keep the data.  

Then you need to make a query that will pad the variable fields.  

Numeric values are right justified and zero-filled.  So if you need 9 digits, then 2356 becomes 00002356.  You do that with the Format function -
... Format(SomeField, "000000000").  Use as many zeros as you need to make the field the desired length which in the example is 9 characters.

Text values are left justified and space filled.  The process of space filling is a little more complicated since it requires multiple nested functions.  
... YourField & Space(10 - Nz(Len(YourField), 0))

the number in the space function is the desired length.  the Nz() and Len() are used to figure out how long the field is now so if the length is 5 characters, you'll end up with abcde.....  with the dots being spaces.

Then just export the query.
0
 

Author Comment

by:PipMic
ID: 40492106
Coachman ...lolol...thanks
0
 

Author Comment

by:PipMic
ID: 40492144
Thanks all,

I am going to work on it..... I'll get back as soon as I can

PatHartman

You have highlighted a concern i have, i.e. padding and space filling:

Could you explain this :-

<<Text values are left justified and space filled.  The process of space filling is a little more complicated since it requires multiple nested functions.  
... YourField & Space(10 - Nz(Len(YourField), 0)) >>

Thanks
0
 
LVL 37

Expert Comment

by:PatHartman
ID: 40492201
I explained it but I'll try again.  To "fill" a field with spaces, my example used three functions.  Space() - which returns the specified number of space characters which in the example would be 10.  But, in a file of fields of random width, we can't know ahead of time exactly how many filler characters a particular field in each row will require.  That is where the Nz() and Len() come.  Len() tells us how long a value is so Len("abc") would be 3 but Len() returns null if the field is null so we need Nz() to substitute a 0 when the length of the field is null.  Since 10 is the actual fixed length of the field, we subtract the value returned by the len() function to know how many space characters we need to append to any field.

So if the value is "xyz", Len() will return 3 so 10 - 3 = 7 space characters will be concatenated.
If the value is "abcdefghij", Len() will return 10 so 10 - 10 = 0 so no additional space characters will be concatenated.
If the value is null, Len() will return 0 so 10 -0 = 10  space characters will be concatenated.
0
 

Author Comment

by:PipMic
ID: 40492250
Thanks
0
 
LVL 29

Assisted Solution

by:Olaf Doschke
Olaf Doschke earned 100 total points
ID: 40493275
>Therefore the data held in the original CSV file would  insert in specific locations of that long record.
Then you really want to know how big you have to dimension the fields/columns of a database table, to be able to import all the CSV data without losing any char, right? Why then ask about a fixed width "file"?

A very simple approach would be creating a table with very large variable length fields, eg all text fields, read the csv data into it and determine max length of trimmed values for each table column. Then you can deduct the needed table for this.

But that would still only be true for that specific csv file. Talk about data export/import in general, if you get csv files on a regular basis, next time some field might have a longer value than you got anytime earlier. Either the structure of an origin changed or the origin field was always larger but only recently has such a long value in it.

In the end a csv file never is complete in that respect and you either have to make assumptions or ask for specifications accompanying the csv file. As a sidenote (rant) I think it's a tendency also very often true for xml given without any xsd structural information, eg max sizes.

So, finally, if you make such a program finding the max widths of all csv values, you still won't have a final truth. In the end you will have to make assumptions or ask for specifications.

Bye, Olaf.
0
 

Author Closing Comment

by:PipMic
ID: 40526742
all valid comments
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.

751 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question