UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Append Query, Any Versions    
 
   
Quinto1
post Feb 27 2018, 02:13 PM
Post#1



Posts: 63
Joined: 23-April 16



I am appending information from 20 files in CVS. At the top of each file there is information that will not append but on column 2 and row there is a word that I would like to appear for every record happened from that file. These woird will change often. See attached. Is this possible?

Thank you

Attached File(s)
Attached File  Capture.PNG ( 34.05K )Number of downloads: 7
 
Go to the top of the page
 
doctor9
post Feb 27 2018, 02:18 PM
Post#2


UtterAccess Editor
Posts: 18,324
Joined: 29-March 05
From: Wisconsin


Quinto1,

Can you post an example text file and describe the table structure that you want to import into? This can be done by basically reading the text file one line at a time, parsing the line into separate data values and appending the data one line at a time.

Hope this helps,

Dennis

--------------------
(;,;) Li'l Cthulu says: Please talk about what you're trying to do, as well as how you're doing it.
Changing your real table name to "Table1" and your real form name to "Form1" in your posts makes it more difficult to understand what's going on, not easier.
Guidelines for Posting Questions
Go to the top of the page
 
zaxbat
post Feb 27 2018, 02:26 PM
Post#3



Posts: 1,035
Joined: 26-January 06
From: .....the wiregrass (either you know or you don't)


Correct me if I'm wrong, but it looks like you are attempting to emulate an excel spreadsheet using Access.....we have come full circle.....

--------------------
Kindest regards, and Cheers!
ZAX

A picture is worth a thousand words and a zipped DB is worth a thousand pictures.
Oh, and....please don't disappear into the Twilight Zone.... Holler back with your results!
Go to the top of the page
 
Quinto1
post Feb 27 2018, 02:40 PM
Post#4



Posts: 63
Joined: 23-April 16



The attached to the post is the spread sheet I will import. I have 20 of them every month that will total about 20,000 records.
I cannot control what will be in column 2 field2 and row 2 but that is what distinguish the route number and I need to run report by route and multiple route or stops.
The info is appended to a table with only 5 control fields.

Go to the top of the page
 
doctor9
post Feb 27 2018, 02:54 PM
Post#5


UtterAccess Editor
Posts: 18,324
Joined: 29-March 05
From: Wisconsin


Quinto,

If you provide me with the names of the table and the fields and the datatypes of those fields, I can provide you with a fairly straightforward bit of code, as I am often having to deal with "CSV" files that have a few extra lines of non-CSV data at the top.

The number of files/records doesn't matter. But if you want more than a general logic structure, I need more info about your database. For example, do you place the text next to "Route" in a Short Text field named "strRoute"? Is Bus Name numeric or text? Is the name of the field "strBusName"? Or "intBusName"? Or "Field4"?

Hope this helps,

Dennis

--------------------
(;,;) Li'l Cthulu says: Please talk about what you're trying to do, as well as how you're doing it.
Changing your real table name to "Table1" and your real form name to "Form1" in your posts makes it more difficult to understand what's going on, not easier.
Guidelines for Posting Questions
Go to the top of the page
 
kfield7
post Feb 27 2018, 03:11 PM
Post#6



Posts: 859
Joined: 12-November 03
From: Iowa Lot


(I assumed you wanted to import into Access, but you don't actually say that. You could also simulate this with Excel tables, but not my preference anyway.)
**Added -- this structure can accommodate changes to the schedule because the primary key is the autonumber. However, it may be difficult to select the correct schedule PK so a ScheduleEffectiveDate field would help, select only the latest schedule as of the run date.**
Create tables tblImportXL, tblStopNames, tblRouteNames, tblDrivers, tblBuses, tblSchedule, tblRun

tblImportCSV
ImportPK autonumber
Route text
StopID text
Driver text
BusID text
ScheduleTime text (convert to time later)

tblStopNames
StopPK auto
StopID text (you're showing a number, but in the future?)
StopName text
{optional GPS coords fields?}

tblRouteNames
RoutePK auto
RouteID text (optional? you have stop IDs...)
RouteName text

tblDrivers
DriverPK auto
DriverName text {or DriverLast, DriverFirst, etc.}
DriverLicense text

tblBuses
BusPK auto
BusID text (you're showing a number, but in the future?)
BusName text (optional?)
BusLicensePlate (optional?)

tblSchedule
SchedulePK auto
SchedRouteFK longint (linked to tblRouteNames.RoutePK)
SchedStopFK longint
ShedTime text or time

tblRun (actual data)
RunPK auto
SchedFK longint
DriverFK longint
ActualTime date&time


Set up a folder where the 20 CSV will be placed.
Create a VBA import routine

2. create list of the CSV files in the import folder
3. for each CSV file
3.a. Read the route name. If not in the route list, add.
3.b. Read the Stop ID, Driver Name, Bus Name, Departure time.
3,c, Add RouteName, Stop ID, DriverName, Busname, Departure time to the Import table
3.d. If Stop ID, Driver Name and/or Bus Name are not in their tables, add them.
4. Preview the Import table.
4.a. Edit, or
4.b. Accept data, placing corresponding IDs and date/times into tblRun.
5.build a query based on tblSchedule and tblRun to compare the scheduled time to the actual time.
This post has been edited by kfield7: Feb 27 2018, 03:26 PM
Go to the top of the page
 
Quinto1
post Feb 27 2018, 04:26 PM
Post#7



Posts: 63
Joined: 23-April 16



This information comes from a vehicle tracking program but they do not have a file that contains all trips or it is not available. Trips are stored by route and use a stop numbers. I can only download one route at a time. The file has a long name containing the from to date and would require updating the link to Access for every download from the tracking program.
To get around that and save time I download all 20 routes into the default directory Download. Highlight all 20 files and rename then to Route and the number 1 to 20 is added, each month automatically will be overwritten and imported/appended to an access table. My problem is that unless all the tables are saved in the same order they will be renamed differently and the records will not match the actual route records. I need to capture that route name during the appending
Because I do not use the headers the field names are added as field1 field2------
-
RouteName to RouteName This is the field that I need to populate
Field1 to Stop
Filed2 to StopName
Filed4 to Bus
Filed5 to SDate
Filed5 to ActPU
Field6 to SchPU
Filed7 to Diff
The upload to append is done by 20 append queries executed by a macro
Go to the top of the page
 
zaxbat
post Feb 27 2018, 04:55 PM
Post#8



Posts: 1,035
Joined: 26-January 06
From: .....the wiregrass (either you know or you don't)


In your first example....do you say that the route name is in the 2nd row and 2nd column? Was it St. Charles B......? So that is the route name that you need? Or did I misinterpret something?
If that is true...I see that the literal text "route" is in the field immediately before the route field. Is that true for each file? If so, then it seems very easy to get the route name. Just open the file for lineinput and parse until you fine "route" and you know the next field will be the route name. So you have the name. Close the file and continue with your query knowing that you have the right route name no matter what order the files are in.

--------------------
Kindest regards, and Cheers!
ZAX

A picture is worth a thousand words and a zipped DB is worth a thousand pictures.
Oh, and....please don't disappear into the Twilight Zone.... Holler back with your results!
Go to the top of the page
 
zaxbat
post Feb 27 2018, 05:03 PM
Post#9



Posts: 1,035
Joined: 26-January 06
From: .....the wiregrass (either you know or you don't)


Does each route have its own query.....is the sequence of the data different for each route?....So you need to know which route so you can use the right query. Or is the problem that the route name is in the header data and you throw that away...thereby losing reference to which route? Just still trying to understand what you need...

if the data isn't proprietary or classified....can you just attach a copy of it....sanitize one if you need to. You may be able to open if with notebook since it is all text and has crlfs. Just go in and garble any sensitive data.
This post has been edited by zaxbat: Feb 27 2018, 05:07 PM

--------------------
Kindest regards, and Cheers!
ZAX

A picture is worth a thousand words and a zipped DB is worth a thousand pictures.
Oh, and....please don't disappear into the Twilight Zone.... Holler back with your results!
Go to the top of the page
 
Quinto1
post Feb 28 2018, 06:41 AM
Post#10



Posts: 63
Joined: 23-April 16



I think it be easier if I create 20 excel sheet and read the CSV file and insert the expression to capture =$c$2 from the external sheet.


I tried to attached the excel but it would not work.

Thank you
This post has been edited by Quinto1: Feb 28 2018, 06:49 AM
Go to the top of the page
 
zaxbat
post Feb 28 2018, 06:44 AM
Post#11



Posts: 1,035
Joined: 26-January 06
From: .....the wiregrass (either you know or you don't)


If you attached something here....I do not see it....

--------------------
Kindest regards, and Cheers!
ZAX

A picture is worth a thousand words and a zipped DB is worth a thousand pictures.
Oh, and....please don't disappear into the Twilight Zone.... Holler back with your results!
Go to the top of the page
 
doctor9
post Feb 28 2018, 09:18 AM
Post#12


UtterAccess Editor
Posts: 18,324
Joined: 29-March 05
From: Wisconsin


Quinto,

Based on your description (I assume there was at least one typo regarding "SDate", and I couldn't find your table name), here's what I'd use:

CODE
Public Sub ImportFile(strPathAndFilename As String)

    Dim strRoute As String, varValues As Variant, strSQL As String, TextLine As String
    Dim intLines As Long, intLine As Long
    
    intLines = FileLines(strPathAndFilename)
'   Activate the built-in progress bar at the bottom of the Access window
    SysCmd acSysCmdInitMeter, "Progress: ", intLines

    Open strPathAndFilename For Input As #1

    Do While Not EOF(1)
    
'       Read a line of text into a variable
        Line Input #1, TextLine
        intLine = intLine + 1
'       Update the progress bar
        SysCmd acSysCmdUpdateMeter, intLine
        
'       Grab the Route name from the headers
        If Left(TextLine, 6) = "Route," Then
            strRoute = Trim(Mid(TextLine, 7))
        End If
        
        If Trim(TextLine) = "" Then
'           Found the blank line between the headers and the data

'           Read past the headers
            Line Input #1, TextLine
            
            Exit Do
        End If
        
    Loop
    
'   At this point, every line we read will just be comma-separated values
    Do While Not EOF(1)
        
'       Read a line of text into a variable
        Line Input #1, TextLine
        intLine = intLine + 1
'       Update the progress bar
        SysCmd acSysCmdUpdateMeter, intLine
        
'       Parse the comma-separated line into separate values
        varValues = Split(TextLine, ",")
                
'       Create the Append query that adds these values to the table
        strSQL = "INSERT INTO tblBusLog " & _
                 "( RouteName, Stop, StopName, Bus, " & _
                 "ActPU, SchPU, Diff ) " & _
                 "VALUES ( """ & strRoute & """, " & Trim(varValues(0)) & ", """ & varValues(1) & _
                 """, """ & Trim(varValues(2)) & """, #" & _
                 varValues(4) & "#, #" & varValues(5) & "#, " & varValues(6) & ");"
            
'       Run the Append query
        CurrentDb.Execute strSQL, dbFailOnError
                
    Loop
    
'   Close the CSV file
    Close #1
    
'   All lines of text have been read from the file, so remove the progress bar
    SysCmd acSysCmdRemoveMeter
    
    MsgBox "Done importing " & strPathAndFilename & ".", vbInformation
    
End Sub

Public Function FileLines(strPathAndFilename As String) As Long

'   This function finds out how many lines of text are in a text file, to aid the progress bar

'   Adapted from code found here:
'   https://stackoverflow.com/questions/13598691/read-number-of-lines-in-large-text-file-vb6

    Dim buff() As Byte
    Dim hF As Integer
    Dim i As Long
    
    hF = FreeFile(0)
    
    Open strPathAndFilename For Binary Access Read As #hF
    ReDim buff(LOF(hF) - 1)
    Get #hF, , buff()
    Close #hF
    
    For i = 0 To UBound(buff)
        If buff(i) = 13 Then FileLines = FileLines + 1
    Next

End Function

The FileLines function isn't strictly necessary, but I included it so I could put in a simple progress bar to show the user that the code has not locked up, and it is really working.

If the file names are predictable, you'd basically call this subroutine like this:
CODE
ImportFile "E:\Download\RideInfo.csv"


If you need to browse for the file because the filename changes, you could use code like this, from The Access Web.

Hope this helps,

Dennis

--------------------
(;,;) Li'l Cthulu says: Please talk about what you're trying to do, as well as how you're doing it.
Changing your real table name to "Table1" and your real form name to "Form1" in your posts makes it more difficult to understand what's going on, not easier.
Guidelines for Posting Questions
Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    15th July 2018 - 10:13 PM