Modules & VBA :: CSV File Manipulation - Parsing Data Into Array
Jun 9, 2013
I'm trying to parse the following into an Array by splitting the csv file using a "," comma separator. There should be 63 different data pieces in this File. When I do a count of them from the (ubound array) i only get 54. The last data piece on each row gets concatenated to the first data piece of the next line. Is there a way to stop this from happening? This is causing problems with working with the data.
I've been working on a procedure to step through the recordset and add the data one record at a time so I can get rid of the duplicates.
I've tried a few approaches, but this is where I'm at now.
Code:
Dim rs As DAO.Recordset Dim rsHH As DAO.Recordset Dim rsPhone As DAO.Recordset Dim rsEmail As DAO.Recordset Dim rsAddress As DAO.Recordset Dim rsPerson As DAO.Recordset Dim db As DAO.Database Set db = CurrentDb
I've done some basic work with arrays.. writing array data to form list objects.. How to use an array as a data source for a report?
Would i need to create a recordset and populate it with the array, then bind the report to it ?
The reason I am asking is the previous developer here built every app using arrays and UDT's... the apps are completely disconnected from the data. Everything is loaded in to arrays..
Is there anyway to strip all characters except numeric out of a value? For example: 999/777-5555. I want to strip the / and the hyphen. The characters can vary so is there anyway to recognize just numeric?
...which I have to crosstabulate, reorder, recode and place in a new table.
Currently I use two queries in Access to do this, but it takes a long time as there are invariably a million or so records.
I've tried to use VBA to speed things up, but with limited success.
Firstly I tried to do a cross-tab and append for each unique identifier...
Code:Sub PopCrossReorderRecode1()Dim db As DatabaseDim rsAbres As RecordsetDim rsCrosstab As RecordsetSet db = CurrentDb()Set rsAbres = db.OpenRecordset("SELECT [OPIE id] FROM Abres GROUP BY [OPIE id]")For Each Value In rsAbresSet rsCrosstab = db.OpenRecordset("TRANSFORM Max(Abres.[Susceptibility result description]) AS [MaxOfSusceptibility result description] " & _ "SELECT Abres.[OPIE id], Max(Abres.[Susceptibility result description]) AS " & _ "[Total Of Susceptibility result description] " & _ "FROM Abres " & _ "PIVOT Abres.[Antibiotic name];") DoCmd.RunSQL "INSERT INTO CrossReorderRecode ( [OPIE id], Tested, Ak, pA, Ctx, Ctr, Cfx, Cx, Cfd, C, Cp, Co, Fu, G, A, S, T, K, CpL, Nx, Ne, Sp, pS, Su, gT, Tm )" & _ "SELECT rsCrossTab.[OPIE id], rsCrossTab.[Total Of Susceptibility result description], " & _ "rsCrossTab.AMIKACIN, rsCrossTab.AMPICILLIN, rsCrossTab.CEFOTAXIME, rsCrossTab.CEFTRIAXONE, " & _ "rsCrossTab.CEFUROXIME, rsCrossTab.CEPHALEXIN, rsCrossTab.CEPHRADINE, rsCrossTab.CHLORAMPHENICOL," & _ "rsCrossTab.CIPROFLOXACIN, rsCrossTab.COLISTIN, rsCrossTab.FURAZOLIDONE, rsCrossTab.GENTAMICIN, " & _ "rsCrossTab.[HIGH LEVEL AMPICILLIN], rsCrossTab.[HIGH LEVEL STREPTOMYCIN], " & _ "rsCrossTab.[HIGH LEVEL TETRACYCLINE], rsCrossTab.KANAMYCIN, rsCrossTab.[LOW LEVEL CIPROFLOXACIN], " & _ "rsCrossTab.[NALIDIXIC ACID], rsCrossTab.NEOMYCIN, rsCrossTab.SPECTINOMYCIN, rsCrossTab.STREPTOMYCIN, " & _ "rsCrossTab.SULPHONAMIDE, rsCrossTab.TETRACYCLINE, rsCrossTab.TRIMETHOPRIM " & _ "FROM rsCrossTab;"NextEnd Sub
...but just got a run-time error 3251 or an error with the transform statement.
Next I tried to address each antibiotic at time...
Code:Sub PopCrossReorderRecode2()DoCmd.SetWarnings off' Delete the current contents of CrossReorderRecodeDoCmd.RunSQL "DELETE CrossReorderRecode.* FROM CrossReorderRecode;"'Populate OPIE id in CrossReorderRecode from AbresDoCmd.RunSQL "INSERT INTO CrossReorderRecode ( [OPIE id] ) SELECT Abres.[OPIE id] FROM Abres GROUP BY Abres.[OPIE id]", dbOpenTable' Run an update query for each antibiotic, putting the relevant code into the relevant field'AmpicillinDoCmd.RunSQL "UPDATE CrossReorderRecode INNER JOIN Abres ON CrossReorderRecode.[OPIE id] = Abres.[OPIE id] " & _ "SET CrossReorderRecode.pA = IIf([Abres].[Susceptibility result description]=""RESISTANT"",""A"", " & _ "IIf([Abres].[Susceptibility result description]=""INTERMEDIATE"",""pA"", " & _ "(IIf([Abres].[Susceptibility result description]=""SUSCEPTIBLE"","""",""."")))) " & _ "WHERE (((Abres.[Antibiotic name])=""AMPICILLIN"")); "'High level Ampicillin'etc etc
...and whilst this worked it took twice as long as the original query, which I imagine is do to its repetitive nature.
If anyone is able to point me in the right direction I'd be grateful. I'm relatively new to VBA and finding it useful and frustrating in equal measures!
I have several thousands of lines of data which I wish to manipulate programmatically, if at all possible. I think that all of the possible permutations are summed-up by the following examples :
123 A text string 2-8 Another text string A-C Another text string here 3-20 And some more text 3A-126B More text Some text without any numbers or letters at the left
What I need to do is :
Move the alphanumeric data at the left to the right. So the data would end up looking like this :
A text string 123 Another text string 2-8 Another text string here A-C And some more text 3-20 More text 3A-126B Some text without any numbers or letters at the left
I suppose it hinges on identifying where the first space in from the left appears, cutting the string at that point, and
We publish a PDF file every week full of taskings that our subordinate organizations need to accomplish for the following week. I would like to have parse the PDF and store the data in a table. I think the only way to start this operation is to first save the PDF to Text (Plain) as this create some way to delimit and parse the data. So, once it's saved as a txt file, it basically looks something like this:
Code: // Column 1 Data/StuffIDontCareAbout/ StuffIDontCareAbout/StuffIDontCareAbout//Column 2 Data StuffIDontCareAbout/StuffIDontCareAbout/ Column 3 Data
[Code] ....
...and so on and so forth anywhere between 50-100 more times
The entire text file will always start and end with "//" at the top and bottom. You also see that each tasking paragraph (starting with "Column 1 Data" and ending with "Column 3 Data") is preceded by a "//" on its own line, and also followed by a "//" on its own line. The text file will always look exactly like this with each tasking paragraph having the same number of forward slashes.
Column 1 Data always starts on a new line under "//" and runs until the first "/" (single forward slash). Column 2 Data always starts after the "//" on the 2nd paragraph line and runs until the end of the paragraph line Column 3 Data always starts on the 4th paragraph line and runs until the end of the paragraph line
Here's a quick example of what I'm talking about:
Code: // 37 NOS001/DCO/ TaskPer/TBD//310001ZAUG2014-292359ZSEP2014 GenText/Remarks/ (U/FOUO) This will contain the actual task description and details. You can see that "(U/FOUO)" contains a forward slash.
[Code] ....
So after it's parsed, my table would have the following new rows:
Code: Column 1 | Column 2 | Column 3 37 NOS001 |310001ZAUG2014-292|(U/FOUO) This will contain 582 NOS012 |280001ZAUG2014-022|(U/FOUO) This another task
I have tables that are ODBC linked to a SQL database. The source tables have record date and time stamps written into one field. This means that I can't use the Access date filter functions on these fields. Is there an easy way to parse this field into two fields, one each for the date and time?
I have a form in which the user has the privilege to download some data from the table.the user will paste some of the numbers to the textbox from the excel in order to download the specific data.i need to write a query to parse the textbox using the in condition
I have some code that successfully gathers some items and adds these items to a listbox, one item at a time using .AddItem.And it works ok.The problem is, when there are a bunch of items to add, everytime the .AddItem runs, the form redraws.there are a bunch and this leads to a lot of flicking while the form redraws however many times .AddItem adds an item.
I've had the idea of building an array and then assigning the array to the listbox.I can build the arrray no problem but i have not been able to assign the array to the listbox all at once.how to do is add items to the listbox from the array one specific item ListArray(i) at a time.
Obviously this would work, but gains me nothing in terms of the visual effect in the form becuase it is still adding to the listbox one item at a time.Is there some way to assign an array to a list box all at once, without doing it one item at a time?
How do I delete values in an array? I want to reuse that array but first I need to delete its values so I would start with Arr(0) rather than where I stopped off with the last loop at Arr(10) [assuming the last calculation inserted until Arr(9)]
Here is my code:
Set rst = CurrentDb.OpenRecordset( _ "Select * from dbo_ProductStructure where ChildProductNbr Like '*" & txtPartNumber & "*'") 'search associated fields with user input While rst.EOF = False ReDim Preserve Arr(i) Arr(i) = rst.Fields("ParentProductNbr") i = i + 1 rst.MoveNext
I'm trying to store all the OrderNumber + Item combinations in 2 arrays and then because the OrderNumber column really contains 2 values I'm interested in, I split it up and store that column's values in 2 arrays. So in total, I have 3 arrays. An array for Item, an array for Order, and an array for RepId (which is the one that I split up from the OrderNumber column).
Anyways, when I print the RepId array with the ' MsgBox PostValCol1(x) ' It prints 4 values like it's supposed to. But when I tested it again by looping through the values and just doing a MsgBox, It goes for a long time and that's because it has a lot of empty values in that array. How to get rid of those empty values/not store them in the first place?
Code:
Set rop = CurrentDb.OpenRecordset("Select OrderNumber, ItemNumber From dbo_EntryStructure Where (ProductNumber = '" & txtPartNumber & "') AND (ActionCode = 'I')")
While rop.EOF = False ReDim Preserve ArrRepOrder(j) ReDim Preserve ArrItem(j)
I have an Access Database that I will be using on a desktop. I have a table in this database that mirrors the structure of a table on a remote server in a SQL database. I have successfully created a vba function within the Access database that uses a server-side php subroutine to select records (I usually won't know how many) from the SQL database and return them to the access database. The code I use in the access vba subroutine to access the php subroutine is:
Code: With CreateObject("Shell.Application").Windows Set ieWindow = CreateObject("InternetExplorer.Application") ieWindow.Visible = False apiShowWindow ieWindow.hwnd, SW_MAXIMIZE ieWindow.Navigate "Web address for server-side php file" End With
The last command in my php subroutine is "return $retrievedData." $retrievedData is a multidimensional array containing data from 42 fields in multiple records (again, I usually won't know how many). I've checked the data in php so I know it has been stored correctly.how do I access the returned data within my access vba subroutine?
I should add that my overall reason for doing it this way is that I want to maintain my server-side database as an untouched master. Users can only add data to it. My client-side database is used to update the input table and further process the data. The subroutines described above are intended to retrieve "new" records only (i.e., records posted since the last access database update) from the server-side database and transfer them to the access database for further processing.
I have a boolean array, foundState(3), whose 4 elements correspond to 4 variables describing conditions that will dictate what action is taken upon closing a form.
There are only 6 possible outcomes for the array, and they can be divided into just 4 cases:
Case {T,T,T,T}
Case {T,T,T,F} OR {T,T,F,F} OR {T,T,F,T}
Case {T,F,F,F}
Case {F,F,F,F}
What the proper syntax would be for this if I'm trying to create a "Select Case" statement for these 4 cases.
how I can make an Array and For Each statement work together. I normally don't use either, but it would be good so I could write less code where plausible. Here is the example I have:
Code: Dim Named As Variant Dim Ctl As Control Named = Array(Me.Namee, Me.ID, Me.Title)
[code]....
I am unsure how I include the array in this statement. I have tried replacing "Me.Controls" with the array name, but I get a 424 runtime error (no object). What I am trying to accomplish is for each item in the array I want to make it not visible if it has a null value.
I have to deal with string arrays that store text. I need info on copying, comparing, appending arrays. Also on passing arrays as parameters to subs or functions. Where I can get to this info quickly without having to browse through many screens.
In addition to this I have some questions:
I have to find the duplicates of values in an array. Here is the code that I use.
Code: Sub FindDuplicates() Dim I As Integer, J As Integer, IEND As Integer, text() As String ReDim text(IEND) For I = 1 To IEND - 1 For J = I + 1 To IEND If text(I) = text(J) Then text(J) = "" Next J Next I End Sub
It works but is not performing well. Are there more efficient ways of doing this?
I'm trying to upload a csv file into an Array then add records to a table. I have the following code which gets the information from a csv file which works fine.
Open filePath For Input As #1 Do While Not EOF(1) Line Input #1, MyData
Problems: 1. When i try to load this into an array , it does not return all the information. It will if a smaller amount of data is sought. For example if only 1 months month of data is requested. Is there a maximum data limit that can be parsed into an array with this method?
2. I'm not quite sure how to parse individual lines (records) to update a table in access. I have provided the code that I'm trying to use to accomplish this below. From code below am I doing this correctly?
3. For each record that is created in the database I would like to add a ticker string to the record for later querying. Can this be done and am I on the right track from the supplied code?
Private Sub ImportData(filePath As String, ticker As String) 'On Error GoTo Errorhandler Dim arrData() As String Dim MyData As String Dim i As Integer Dim Db As DAO.Database
I've created an array that I created and declared as a Public array in my module. I created a function that populates the array so that I can use the values in another function. I've gotten the array to populate but when I go to use the values in the array in another function, the array appears at Empty. I seem to be stuck on declaring it properly or something so that it can be used by other functions.
Public arrWebIDs As String Public Function FillArray()
