Browse to file (DAT) & Import fixed column widths

I have scoured the web and tried out many potential codes, but nothing works for me. I found the code below over at OzGrid, and I made changes to the array to suit my needs, and a few other changes (file type was txt, I changed to DAT, as this will be used by someone else). I have spent 2 days on trying to do this myself (Friday and today). I am guesing that I am close, just not quite there. Any help is greatly appreciated!

My data is as follows:
Start: 0, 1, 10, 13, 23, 29, 37, 46, 49, 58, 59, 61, 63
Length: 1, 9, 3, 10, 6, 8, 9, 3, 9, 1, 2, 2, 3

The data file ends at 66 characters.

I would like all columns set as General, except the following:
2, 3, 4 - Text
6 - Date (MDY)

Please Login or Register  to view this content.

I'd recommend uploading sample .DAT file (or .txt).

For fixed width import, you need different syntax than standard field array using Text Import Wizard.
You'll need to set .TextFileColumnDataTypes = Array(#, #, #....) as well as .TextFileFixedColumnWidths = Array(#, #...) and other properties.

You can try recording process of manually importing the file to get the base syntax.

CK76,

Unfortunately I only have the one (confidential) DAT file. And Excel will not me to use the Text Import Wizard, it forces me to do a query. so I cannot go through the steps to record as you suggest and also see the data to provide s sample file.

Can't you desensitize the info? From your description, you just changed file extension from .txt to .DAT. So you have access to the structure of the file and what's in it.
Trim it down to about 10 lines, and replace sensitive info with generic one. While maintaining the structure. That should give us enough to work with.

CK76---

Actually I ha not changed the file from DAT to TXT, so I just finally did that to provide a sample. Attached is a sample file consisting of approximately 10 rows of data. What will become my template will have headers in row 1, so when imported I would like this data to start on row 2. However I would like the current top and bottom rows of the file to be deleted. They are info that the source uses that have no meaning for us. Please remember that I have this set for a DAT file. I want the user to only select the correct file; you can select "all files" to allow selection of the TXT file.


Kenneth---

I changed FName to vPath as you suggested, however the same error showed.

I will look into making the standard method more tailored to your needs. For now, try recording a macro to see if it does what you want suggested in post #2.

You can do the math to make the fixed list needed or use this. After a run, the result is in the Immediate window. Or, copy the comment less the single quote for use in the recording. I think that the array string needs a tweak.

Please Login or Register  to view this content.

I guess you know how to record a macro?

To do the manual import, in a new sheet on the ribbon: Data > Get Data > From File > From Text/CSV > select your file > for delimited, choose Fixed Width > below it paste, 0,1,10,13,23,29,37,46,49,58,59,61,63,66 > Transform Data > Load.

Here's basic code.

I've got to go into meeting and don't have time to adjust for formatting. But it should give you good starting point.

Please Login or Register  to view this content.

Kenneth,

I added the Legacy Text Import back in. For this project, the power query causes issues with the data. Although I try to select the format for a few columns, the text is no problem. The issue is with my one date column. Power Query deletes the leading 0, and then gives an error, so the data in that field will not convert to a date. When I use the "legacy" text import wizard (which is easier for the user), that does not happen, and this date field remains a date. Yes I know how to record a macro. The recording below is when I used my DAT file and use of the text import wizard.

Please Login or Register  to view this content.

Since date string is just numeric string that does not conform to standard formatting convention. You'll need bit of special operation.

For Numbers that should be stored as text. You can simply set cell's number format to "@".

Ex:

Please Login or Register  to view this content.

Note: You can adjust starting row of data storage by adding last row (first empty row) # to j.

CK76,

That works well except 2 things:

1) column G data is somehow being formatted as a date also, it should be general. Only column F is a date (and only B, C, D are text).
2) I need the ability to browse for a file; hard coding the file name (changes every week) and path (multiple users) is not an option

Now I also have a question. I am attempting to understand the syntax of your arrays in this script. I see the 2nd # relates to the length, but I cannot figure out the first #, as it does not match what I provided. Is this a better way to do it? Oh, 1 other question. In your date format for column F, I think I may need to display the date as: MM/DD/YYYY, we are waiting for confirmation. I see the date in the "case 5" area, but I am unfamiliar with that format. How would I change it to reflect the format I mentioned?

Column G in my environment does come in as General. Not Date. If you need to just set NumberFormat to general for the target cell when Case 6.

As for using MM/DD/YYYY format for Case 5 (column F).
Format() function is just converting number to date string. When this value is returned to sheet/cell, it's automatically interpreted as Date value. You can apply any date formatting you want to the target cell.
Ex:

Please Login or Register  to view this content.

Sample result:
0.JPG

First # is starting position. In VBA character position starts from 1 not zero. So I just added 1 to your positions

I prefer this method over legacy wizard, as it gives me more control over what transformation is needed for each step.

I'm out of time for now, but to select multiple .DAT files, you can use FileDialog() method. Will see if I have time later.

Here you go. Had bit of time. This will give you file dialog to select files and pre-filtered for *.DAT files.

Please Login or Register  to view this content.

CK76----

That works great! But I was trying to make you proud of me! LOL I was making adjustments to the code for the "browse" option I wanted. I also added the headers, I could not seem to add it to this same script so I created another for that purpose, and called it. It does work, but I have 1 issue I am hopping you might be willing to assist on tomorrow. This macro will be in a new template for the user, and to execute they will just push a button (Easy Peasy!). Now the sheet where the macros/buttons are: MAIN (codename: Sheet2). This data needs to be imported into DAT data tab (codename: Sheet4). Now because the user MAY change the sheet names, I want the macro to reflect the codename (Sheet4) for where this data is to import to. I could not figure out where to add it. I tried a few options, and they simply added on the active sheet. Thanks in advance, you have been awesome in helping me resolve my challenge.

Please Login or Register  to view this content.

Please Login or Register  to view this content.

Kenneth---

I only have one thing to say to you.... WoooHooo!!!!! Thanks so much for finalizing the changes I requested. I greatly appreciate you doing these final changes.


CK76----

Kenneth was able to update your code with my final change request. Thanks so much for everything. Your time and help are greatly appreciated!!!!!

You are welcome and thanks for the rep

If your question is satisfactorily answered. Please mark the thread as solved, using Thread tool found at top of your initial post.