Solved

How to convert a CSV file into a fixed width file

Posted on 2014-12-10
13
402 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
SharePoint Admin?

Enable Your Employees To Focus On The Core With Intuitive Onscreen Guidance That is With You At The Moment of Need.

 

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 38

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 38

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

Does Your Cloud Backup Use Blockchain Technology?

Blockchain technology has already revolutionized finance thanks to Bitcoin. Now it's disrupting other areas, including the realm of data protection. Learn how blockchain is now being used to authenticate backup files and keep them safe from hackers.

Question has a verified solution.

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

Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

623 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