I need to consume this data in a vba code without modifying the spreadsheet I am working with. I figured I would consume it into a dynamic array, but I'm not sure how to do it with different data types.
I have tried the following (just found on the internet)
It works, in that my data is loaded but is not delimited. I need to be able to work with some of the numbers in the file. I unfortunately cannot just load the file into a sheet, though that would be easier. Can anyone help?
Last edited by ifthenifthen; 04-19-2018 at 02:19 PM.
Reason: adding [code][/code]
Re: Importing a delimited text file with different data types into an array
Welcome to the forum, unfortunately your post does not comply with Rule 3 of our Forum RULES. Use code tags around code.
Posting code between [CODE]Please [url=https://www.excelforum.com/login.php]Login or Register [/url] to view this content.[/CODE] tags makes your code much easier to read and copy for testing, it also maintains VBA formatting.
Click on Edit to open your thread, then highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found here
Please edit your first post so we can continue to help you.
Avoid using Select, Selection and Activate in your code. Use With ... End With instead. You can show your appreciation for those that have helped you by clicking the * at the bottom left of any of their posts.
I unfortunately cannot just load the file into a sheet, though that would be easier.
Yes easier and of course you can in a temp worksheet or in a temp workbook !
Originally Posted by ifthenifthen
I need to be able to work with some of the numbers in the file.
Quite same as CK76 but without last empty row if exists in the source text file,
run the next demonstration and see in Locals window the content of the V array variable …
Here INDEX worksheet function extracts all rows and columns from source text file
- as we can't guess what you really need -
but this function can extract part of rows / columns (for example rows #2 to last and columns 5 & 6).
PHP Code:
Sub Demo1() Dim V, W V = ThisWorkbook.Path & "\test.txt" If Dir(V) = "" Then Beep: Exit Sub Open V For Input As #9 V = Array(Split(Input(LOF(9), #9), vbNewLine)) Close #9 ReDim Preserve V(UBound(V(0)) + (V(0)(UBound(V(0))) = "")) For W = UBound(V) To 0 Step -1: V(W) = Split(V(0)(W), "|"): Next W = Evaluate("COLUMN(" & Cells(1).Resize(, UBound(V(0)) + 1).Address & ")") V = Application.Index(V, Evaluate("ROW(1:" & UBound(V) + 1 & ")"), W) Stop End Sub
Do you like it ? So thanks to click on bottom left star icon « ★ Add Reputation » !
Re: Importing a delimited text file with different data types into an array
I'm sorry that I did not better explain what I am trying to do.
Ultimately, I am creating a tool that a user can interact with. The user inputs a file path (always a text file, always the same columns, though the order the columns might be different). The user can input a "Name" like JOE, and then fetch certain data. For instance, I might include a button that returns the sum of all items in the "quantity" column associated with JOE. What I am trying to avoid is pasting the contents of a text file into a sheet so that the tool is cleaner.
I thought that the best way to start this would be to load the text file into an array, and then figure out how to do calculations with it's contents. So far, I am having trouble even returning one line of the array I built with debug.print.
If this is not the best way to go about this task, I would love to hear opinions!
I am experimenting with the options given now - thank you so much.
Re: Importing a delimited text file with different data types into an array
There are few ways to accomplish your need.
1. Use ADO to query text file and have schema file created. This allows you to construct SQL query string to retrieve desired info without need to manipulate text data.
2. Since you have Pro Plus, leverage PowerQuery and pass parameter(s) user enter in cell(s) to query data from text file. This is probably the easiest to maintain, but will require user to hit "Refresh All" before the data is brought in and requires all users to have access to PowerQuery.
3. Use code Marc and I posted to fill array. Then perform lookup operation on array elements using condition provided. Application.Match, Application.Index will be useful here.
It would be helpful if you can upload couple of sample text files (with about 10~25 rows of data) and some desired results. To upload file, use "Go Advanced" button in quick reply menu and find "Manage Attachments" hyperlink. Click on it and it will launch new tab/window for managing attachments.
What I am trying to avoid is pasting the contents of a text file into a sheet so that the tool is cleaner
It' a pity a beginner avoids to work within a worksheet 'cause
- it's the easy way as it's the purpose of Excel
- the code is very light, efficient and easy to maintain using Excel inner features (like formula & filter)
- the use of the (temp) worksheet can be easily hidden during execution …
Originally Posted by ifthenifthen
[…] sum of all items in the "quantity" column associated with JOE.
An array demonstration :
PHP Code:
Sub Demo1JOEqty() Const S = "|", K = "QUANTITY", N = "JOE" Dim V, W, D# V = ThisWorkbook.Path & "\test.txt" If Dir(V) = "" Then Beep: Exit Sub Open V For Input As #9 V = Split(Input(LOF(9), #9), vbNewLine) Close #9 W = Application.Match(K, Split(V(0), S), 0) If IsNumeric(W) Then W = W - 1 Else Beep: Exit Sub For Each V In Filter(V, S & N & S, True): D = D + Val(Split(V, S)(W)): Next MsgBox N & " " & K & " total = " & D, vbInformation, " Text file extraction" End Sub
Do you like it ? So thanks to click on bottom left star icon « ★ Add Reputation » !
Last edited by Marc L; 04-20-2018 at 01:10 PM.
Reason: optimization …
LinkBack URL
About LinkBacks
Register To Reply
Let's play !
Originally Posted by ifthenifthen
Bookmarks