Appending data into a table in message. (1 Viewer)

[Ryobi]

Hello everybody,

Does anybody know how to append data from excel CSV into an existing Msaccess table ?
The vba code I had DoCmd.TransferText acImportDelim, , "InputForm8596Form8597", [Forms]![frmTimeForceReports]![Txtimportfile]
used to work, but I am getting error on the data because the software developer from the csv change the out format of the fields (they all used to be text).
I can manually import the data using the append method, but I do want the to have to be appending it every time. I need put the "" as quailfier (see photo attached) something like this DoCmd.TransferText acImportDelim, "" , "InputForm8596Form8597", [Forms]![frmTimeForceReports]![Txtimportfile] which does work. I was thinking of using the insert sql statement but how to I tell it get the data from the excel file ? I was hoping for something like this DoCmd.Inserintotable acImportDelim, "" , "InputForm8596Form8597", [Forms]![frmTimeForceReports]![Txtimportfile] . Any ideas ?

Thanks

 

[theDBguy]

Hi. Importing text files usually use pre-defined import specifications. Maybe you just need to update the spec you're using now to work with the new data format.

 

[Ryobi]

I was looking in the same direction as what you recommended theDBguy. I had tried that method but it did not work , even if did it that meant having to convert all to fields to the new types or converting import data to a text fields. The trick was to have the data in text format. That is easily done by not export the first row as headers (fields names) and including in the data. Then after teh data is imported removing the headers (See attachment). Anyway, something come to mind and I though there must be something very simple so I recreated the import process and the an created a import specification file. The steps on how to do is found on https://access-programmers.com/creating-an-import-specification-in-access-2003.
The only thing they do not show is where the file is save nor the steps which are bit tricky. Once you save the file you can import in the same statement and specifying the file. My specification file I name "TimeForceData Import Specification". So the new statement should be this DoCmd.TransferText acImportDelim, "TimeForceData Import Specification", "InputForm8596Form8597", [Forms]![frmTimeForceReports]![Txtimportfile]. Hope this helps somebody else with the same issue. Thank you theDBguy for the reply.

 

[moke123]

I've been working with importing tab delimited text files all day today.
I 'm using a saved import spec but executing it in vba.
I found this code from @NauticalGent Changing saved import xml and modified it to import a file selected with a filedialog file picker.

A few times I ran into datatype errors but found that there is a hidden table that holds the spec colums and I was able to change the datatypes there.

 

[vba_php]

Ryobi,

another alternative that might yield less errors, either planned for or unplanned for, is the I/O method using the text file streaming method.

 

[Ryobi]

moke123, I do have diaglog picker to select the import file because the import file may be in any location and you want to user to able change the location without have modify the source code. The way I have is that the import file is saved to the hidden file that is part of the front end of the database along with the user and time. I have that the files are automatically import when the users going into the import screen. If the user decides to move the the data they change used the option (see photo below) where they can change he import file location. Whey they select the new location the data file location is save to the hidden table in the front end of the database.

vba_php your suggestion is good, however it require alot more code. I use that method (actually similar to) when I need to look for specifiy data and/ or concatenation fields since that method is field specific where as the import method is more like an sql statement that aggregates the data. Both methods have their pros and cons. The con to the DoCmd.TransferText is that you can tell not where the error is located because you not can see the contents of the fields (the same hold true for an sql statement) if they do not execute correctly. Even if you trap the errors like I did, you can not see the content of the what is being imported. Hope this helps.

 

[vba_php]

vba_php your suggestion is good, however it require alot more code.

it might require code, ryobi, but MOST of the time it is reliable. but you have to know that VBA, like all legacy languages, has serious flaws to it and is unreliable completely, more or less because MS doesn't pay attention to it. that's always been my guess. here's an example of what I'm currently doing with it in order to automate the code writing process of HTML tables on my website:

Function create_html_table()

On Error GoTo Err_Handle

Dim wPath As String
Dim row As Long
Const wMode = 1& 'Read Only Mode - Long Value

Dim fso As Object
Dim oFile As Object
Dim tStream As Object
Dim strInput As String

wPath = "C:\Users\Owner\Desktop\for-import-to-excel.txt"
row = 1

   Set fso = CreateObject("Scripting.FileSystemObject")
   Set oFile = fso.GetFile(wPath)
   Set tStream = oFile.OpenAsTextStream(wMode)

On Error GoTo Err_Handle
Application.ScreenUpdating = False
    Do
         strInput = tStream.ReadLine
            Range("a" & CStr(row)) = strInput
            tStream.skipline
            row = row + 1
    Loop
Application.ScreenUpdating = False

    Set fso = Nothing
    Set oFile = Nothing
    Set tStream = Nothing
    
Exit Function

Err_Handle:

      Set fso = Nothing
      Set oFile = Nothing
      Set tStream = Nothing

End Function

so as you can see, there are many uses for this. if you want to try it, it's pretty easy to put together a routine using any of the method types of streaming, found in that article I pointed you to. I've even got multiple function routines stored in my knowledgebase that do operations using each one of the streaming method types. let me know if I can help you in that regard and I can post those functions as templates you can use to write your own code.

 

[Ryobi]

vba_php it interesting that you mention that VBA is legacy language, however it the backbone to msoffice. You got me curious you mentioned an HTML table. I have never heard of that. That something that is attached to form or some kind of virtual table ? I see that you have function is it simular to the one that I have in Visual Net to check for the data that contains the location of the data file used by the program.

Sub GetdataFile()
' **** Get Text file *********
Dim file_path As String = Application.StartupPath
file_path = file_path.Substring(0, file_path.LastIndexOf("\"))

If System.IO.File.Exists(file_path & "\DataFile.txt") Then
PubVar = file_path & "\DataFile.txt"
Else
PubVar = file_path & "\DataFile.txt"
System.IO.File.Create(PubVar).Dispose()
Dim file As System.IO.StreamWriter
file = My.Computer.FileSystem.OpenTextFileWriter(PubVar, True)
file.WriteLine("Path: *** Invalid Path ***")
file.WriteLine("User: ")
file.Close()
End If

' **** Get database File *******
Dim fileReader As System.IO.StreamReader
fileReader = My.Computer.FileSystem.OpenTextFileReader(PubVar)
Dim stringReader As String
stringReader = Mid(fileReader.ReadLine(), 7, (Len(PubVar) - 6))
PubVar1 = stringReader
fileReader.Close()

End Sub

 

[isladogs]

I was looking in the same direction as what you recommended theDBguy. I had tried that method but it did not work , even if did it that meant having to convert all to fields to the new types or converting import data to a text fields. The trick was to have the data in text format. That is easily done by not export the first row as headers (fields names) and including in the data. Then after teh data is imported removing the headers (See attachment). Anyway, something come to mind and I though there must be something very simple so I recreated the import process and the an created a import specification file. The steps on how to do is found on https://access-programmers.com/creating-an-import-specification-in-access-2003.
The only thing they do not show is where the file is save nor the steps which are bit tricky. Once you save the file you can import in the same statement and specifying the file. My specification file I name "TimeForceData Import Specification". So the new statement should be this DoCmd.TransferText acImportDelim, "TimeForceData Import Specification", "InputForm8596Form8597", [Forms]![frmTimeForceReports]![Txtimportfile]. Hope this helps somebody else with the same issue. Thank you theDBguy for the reply.

@Ryobi
Since A2007, import/export specifications have been stored as XML and hidden away from view.
However, it is possible to manage them when you know how.
See if this thread in the repository helps View and edit IMEX data task specifications

Hope that helps

 

[vba_php]

vba_php it interesting that you mention that VBA is legacy language, however it the backbone to msoffice. You got me curious you mentioned an HTML table. I have never heard of that. That something that is attached to form or some kind of virtual table ?

you don't know what an HTML table is, Ryobi??? oh my goodness! HTML runs the internet, my friend! (along with a lot of other things) HTML = "Hypertext Mark Up Language"

You said something about "Visual Net"?? what do you mean by that? visual basic DOT NET?? that's a language in the .NET architecture, created by Steve Ballmer when he was running microsoft. you can find it in the visual studio development IDE template files. HTML markup looks like this:

<html>        
<head>    
<link rel="stylesheet" type="text/css" href="../../../../../../../includes/css/programming-languages/vba/resource-lists/html-table.css">
</head>
<body>
<h2>VBA Active X Control Resources</h2>
<table>
<THEAD>
<TR>
<TH>directory_pat</TH>
<TH>file_nam</TH>
</TR>
</THEAD>
<TBODY>    
<TR VALIGN=TOP>
<TD>Microsoft Shared-MSInfo-OINFO12.OC</TD>
<TD>OInfoLi</TD>
</TR>
<TR VALIGN=TOP>
<TD>ms office xx.x-MSCAL.OC</TD>
<TD>MSACA</TD>
</TR>
</TBODY>
</TABLE>
</BODY>
</HTML>

and when you look at it on a page, it looks like this (I didn't include the stylesheet's address here that I'm using):

this website is actually using static PHP pages, but truth be told it can be done much easier by using PHP and pulling the data straight out of a database. but I'm doing it this way to make it easy for visitors. normal people don't understand dynamics like PHP/.NET stuff.

 

[Ryobi]

vba_php, I do know what is HTML, what I am curious as to how HTML is a table. I am thinking that perhaps you are using Mysql and call it from the HTML file. For Visual Basic .Net here is the run down. Visual Basic prior to .Net was were machine dependent and are Form Basic not Oops. When Visual Basic was complied it was (and is) machine specific an you must include and reference libaries in the package and they are assemble and distributed as package. People who program is Visual Basic prior to Visual Basic .Net are purpies and generally do not like Visual Basic .Net because Visual Basic (I believe the last version was 6.0) as faster the Visual Basic .Net. Visual Basic .Net is the new version of Visual Basic after 2002. Alot of People thought that the .Net mean that it was for the internet. The truth is .Net means that it it use Net Frame. Net frame supplies the librarbies and also compiles them into make the programs that able to run an different machine. It sort a like Java runtime for the windows. Also with Net Frame you not can tell whether the source code was written in Visual Basic, C#, J# or any lanagude use by visual Studio. I know how to change, edit and the HTHML tag does but at the end is just a formatting lanuage not an actual programming lanuage. The same be told about SQL, SQL is not a programming langauge is used for retrieve data from the database such Mysql, Msaccess (yes you can use sql statenets on a accb files) or Microsoft Server. I would like to point out one interesting fact about Msaccess it can be used as Front end (to create programs) and a back end to (as a database). One final thought a Msaccess it can be not be compiled (You can not create an exe) even you have read in the internet that you can change the extension to mde. All this does is lock up the forms so the user can not alter them.

 

[vba_php]

hey Ryobi,

thanks for the info man, but I know this! did you get that private message I sent you here? That link I sent will show you literally everything you want to know about VBA. and there's even more to come....I'm working on it constantly. and yes, I'm well aware of the fact that languages in the .NET architecture can run locally as well as on the internet.

HTML has nothing to do with the .NET architecture, however most .NET web applications have HTML somewhere mixed in with it. if they didn't, the web app would look like crap and not structured on the page properly. same is true for cascading style sheets (CSS files) and Javascript script files (.js files)

Most, if not all websites, have all 3 client side languages running on them (HTML, CSS and Javascript). I explain all of this and how they work together on my website.

 

[Ryobi]

Vba_php is the private message in the Email ? Regarding VBA used it because I create Form bases application. I know that web applications are better at dealing with access data from the internet, but web base application lack the reports abilites that the Form base have. That is the reason that I create the reporting program (ms Access) because the web program that they are using has reports do not look very professional. Thank for the information.

 

[isladogs]

Ryobi
Did you see my reply in post #9?

 

[vba_php]

Vba_php is the private message in the Email ?

clicking on the icon you see in the image below will show you who has sent you private messages and it'll give you links to click on them to go there:

you should see it at the top of every page you visit if you're still browsing the forum. and may I say that, even though my website will show the general public much more than they will probably see on this forum, what you really should do is take the advice, go to the links, and take the suggestions of people like isladogs (Colin) before you look at what I can show you, only because they are the "experts" regarding ms access and have worked with the program for many years. I have moved on from working with access long ago. I'm a server/web application guy now. but I still have plenty of access knowledge. so find that icon at the top of your screen and you'll be able to view the content on my site!

 

[Ryobi]

Thanks isadogs and Vba_php .

Vba_php the information you gave me is very useful. Keep up the good work both you.

 

[vba_php]

Vba_php the information you gave me is very useful.

well that's good! just make sure you don't overload yourself. =) when that website is done, there's gonna be so much content on it I might just overload the server!

 

[MajP]

Not sure if this will be useful, but it has a lot of utilities for reading an unformatted text file and trying to parse it using a lot of logic. Then importing the results. Worked pretty well, but the hard part was coming up with the logic to handle all possible exceptions.

Help with text file trim and create 2 tables

Hi! all, I have a unique problem, not sure if there is any solution for this. I have a account statement in txt format which runs in quite number of pages, with plenty of spaces and empty rows but has a standard format across all pages. What i am looking is to get the Output result (refer...

www.access-programmers.co.uk

Help to import text file of multiple formats

HI! i have similar difficulty to import text file to access. The look of the report is different from the last time i had imported a text file. i am wondering if there is a way to capture the text details and provide a vba code which can be used for any type of text file imports. Cause, i...

www.access-programmers.co.uk

 

[vba_php]

Not sure if this will be useful, but it has a lot of utilities for reading an unformatted text file and trying to parse it using a lot of logic. Then importing the results. Worked pretty well, but the hard part was coming up with the logic to handle all possible exceptions.

that's might actually be useful to me, Maj! thanks!

 

[moke123]

Heres the code I slightly modified. I created a saved import spec with the append option , passing the 2 arguments StrNewPath and strSpecName.

Sub ChangeImportPath(StrNewPath As String, strSpecName As String)
    On Error GoTo Err_Handler
    
' MSXML2 requires reference to "Microsoft XML, v6.0"
' earlier versions are probably compatible, remember to use the appropriate
' DOMDocument object  version.
' code borrowed from Jason Hardman's contribution at this link:
' https://stackoverflow.com/questions/23350640/how-to-specify-a-different-file-path-for-a-saved-excel-import/38265498#38265498?newreg=649801888011413a8d9e56e187f953f7
    
    Dim XMLData As MSXML2.DOMDocument60
    Dim ImportSpec As ImportExportSpecification
    Dim XMLNode As IXMLDOMNode
    'Dim StrNewPath As String
    
    ' Get XML object to manage the spec data
    Set XMLData = New MSXML2.DOMDocument60

    
    ' existing Import Specification (should be set up manually with relevant name)
    Set ImportSpec = CurrentProject.ImportExportSpecifications(strSpecName)
    
    XMLData.LoadXML ImportSpec.XML
    
    
    
    ' change it's path to the one specified
    With XMLData.DocumentElement
        .setAttribute "Path", StrNewPath
    End With

    ImportSpec.XML = XMLData.XML
    
    ' run the updated import
    'Debug.Print CurrentProject.ImportExportSpecifications(0).XML
    ImportSpec.Execute

Exit_Handler:
    Set ImportSpec = Nothing
    Set XMLData = Nothing
    Exit Sub

Err_Handler:
    Select Case err.Number
        Case 3011
            MsgBox "Replacement path is invalid", vbCritical 'Profile name is wrong or MS Exchange server is offline
        Case Else
            MsgBox err.Number & " - " & err.Description & vbCrLf _
                   & "Please take note of the error code and contact your System Administrator", vbCritical
    End Select
    Resume Exit_Handler
    
End Sub