Modules & VBA :: How To Import Text File Line By Line
Nov 18, 2014
I am having a little difficulty with my importing in Access. Every time I import my text file, the lines will be jumbled. I have been reading up and I found this recordset code that seems to be what I need:
Code:
Dim strLine As String
Dim intLineNum As Integer
Dim MyDB As DAO.Database
Dim rst As DAO.Recordset
Open "C:TestTest.txt" For Input As #1
Set MyDB = CurrentDb
Set rst = MyDB.OpenRecordset("tblResults", dbOpenDynaset)
CurrentDb.Execute "DELETE * FROM tblResults", dbFailOnError 'Clear tblResults
[code]....
Basically, this code will extract data from the text file as long as it fulfills the Mid$ criteria. Here's where my problem comes. Each line in my text file is of different lengths and I have to capture the entire line.
I think using the Left$ function would work, but I don't know how to determine the character count such that the entire line of text would be inserted into the table.
Another difference between what I need and the code above is that, I am required to store each line into each row of my table, meaning
Line 1 is placed in Row 1 Column 1
Line 2 is placed in Row 2 Column 1
Line 3 is placed in Row 3 Column 1
.
.
.
Line X is placed in Row X Column 1.
I have a module which reads a CSV transaction file line by line and adds the correct transactions to an access table and places the wrong ones in a logfile.Now some transactions are rejected twice there is even one rejected six times. Whereas one wrong transaction is processed only once. I am certainly overlooking something obvious in the logic but what. Here is the relevant code.
Code:
Function ImportCSVForConfederation(inputCSV, ORG) Dim TNO As Integer, TACT As Integer, TABLE As String, TLINE As String, I As Integer, J As Integer, K As Integer Dim FLD1 As String, FLD2 As String, FLD3 As String, FLD4 As String, LogFile As String, LogPath As String Dim Lim As String, ITNO As Integer
I have a function which takes a recordset and writes the contents to a text file. One of the fields in the recordset is a free format text field, the contents of which originate from a text box on a form. Occasionally, a user can hit the 'Enter' key to add a new line (carriage return) to the note within the text box.
The problem is, when the output text file is subsequently imported to Excel, the carriage returns within that text field serve to move the next piece of data to the next row (even though the CR is within the double-quote text-qualifier in the text file)I want line breaks within the text field to be retained within the cell.
One option would very simply be to replace the carriage returns with simple spaces in the write function. I could do that quite easily. However, I would prefer to retain whatever the user has entered into the text box, including line breaks, if at all possible?So if the user broke his free format 'note' into multiple lines (for whatever reason), it would similarly appear as multiple lines in the same cell (and in the same row / record) in the Excel worksheet.
I could replace the carriage return with some obscure character during the write, then switch it back during the Excel import, but that seems a little clunky (plus Murphy's Law dictates that no matter how obscure a character I choose, somebody, at some point, will genuinely use it, and it will get switched out. Which means I'd have to use a non-printing character?could I simply replace the carriage return with a line feed? (Or is it the other way around? I always get carriage returns and line feeds mixed up!)there's a simple way around this but I always get confused as to precisely which ASCII character the 'Enter' key produces when keying into a text box.
I want to open a txt file through my access database and append a line to it. When I use the opentextfile method for appending but it always appends my new text line to the end of the last line. I want it to append the new text to a new line. I have tried object.skipline but this is not supported when appending?
This is what I get: "oldline" "newline"
but I want: "oldline" "newline"
Here is my code
sub ConcatenateTxtFile ()
Set fs = CreateObject("Scripting.FileSystemObject") Set f = fd.OpenTextFile(dpath, ForAppending) 'Need a line here to skip to next line f.writeline ("newline")
I need to insert(append) first and last line in xml file...
So I have xml file and I need to insert one line to the top and at the of the text... Xml file contains Cyrillic characters and method to recreate xml file is going wrong with characters conversions... The last line a can append easy but the first line is a problem...
I have a textbox on continues form which contains multiple lines and I d like to set it up so it ll be scrolled down completely when it displays the form. I ve already found a few so called solutions but nothing worked for me.
If i simply remove the GROUP BY line and stick the semicolon at the end of the previous line (.EmpID; ) it works just fine. How is adding a group by line causing an error?I tried adding another parenthes at the beginning ((( and ending the joins as EmpID); and that failed with the exact same error.
I have a main form with 3 sub forms. The main form is tied to a table called QUOTES_MASTER. The first sub form is tied to a table called QUOTE_ LINE_ ITEMS_DIRTGLUE. It calculates the subtotal when selecting items. The relationship is one-to-many linked on QUOTE_ID.
The second sub form adds up total of all line items and is not tied to a table.The third sub form adds ESTIMATED FREIGHT to the PRODUCT TOTAL and is not tied to a table. how to get the values from the line items form inserted into the QUOTE_LINE_ITEMS_DIRTGLUE table as they are added.
I also want to insert the total value from ESTIMATED DELIVERED into the LINE_TOTALS field in the table QUOTES_MASTER.I tried this code on the product total sub form but it doesn't do anything and there are no errors:
Private Sub PROD_SUB_AfterUpdate() DoCmd.RunSQL "UPDATE QUOTE_LINE_ITEMS_DIRTGLUE SET QUOTE_LINE_ITEMS_DIRTGLUE.SUBTOTAL = Me.PROD_SUB WHERE QUOTES_MASTER.QUOTE_ID = " & Me.QUOTE_ID
I can change multiple things on a line graph with VBA.
Me.Graph47.chartType = GraphType ' take 65 for line with me.graph47 .SeriesCollection(1).border.Color = vbblue ' change the line color .SeriesCollection(1).border.Weight = LineWeight ' change the line weight to for example 3 .SeriesCollection(2).MarkerSize = MarkerWeight ' Change the marker weight, for example 4 .SeriesCollection(2).MarkerBackgroundColor = vbblue ' Change the marker color, .SeriesCollection(2).axisgroup = 2 ' put this series on the secondary axis end with
SeriesColection(1) is line with markers. This is correct.But now I want the seriescollection(2) without line, so only the markers. I cheched the MSDN site from Microsoft. The Excel trick with the macro does not work for me.how to hide the line with VBA for only SeriesCollection(2) in Access?
I am currently importing data using the the "TransferText" method in Visual Basic; e.g., expression.TransferText(TransferType, SpecificationName, TableName, FileName, HasFieldNames, HTMLTableName, CodePage)
Per the above description, I can either include the first line as a field name, or as data. Is there any way to simply ignore this data row and start the import from row 2 onwards?
how to read a specific line in a CSV file (using VBA), to see if the phrase "There are no records available." is present.
If it is present, then I'm going to do a debug.print stating that there are no records to load - and then the script will move on to the next file. If the phrase isn't present, then I'm going to upload the file to Access, parse the information, and then upload it to a CRM. (I already have the latter portion of the code up and running....I just need to account for the first part, where I can determine if the file has data or not).
The structure of the file never changes. The first row is composed of eight column headers (Post Date, Card Number, Card Type, Auth Date, Batch Date, Reference Number, Reason, Amount) and (if) the phrase "There are no records available." is present, it will show up on the second row, in the first column (under Post Date).
One of our vendors needs data from a table with a header (as HD) and a footer (as FT). The header is easy but short of converting the query to a report, how do I send CSV file with a footer as the last line.
I have a field on an Access db, this is a "Memo " type field which has many lines of text within it.
I need to run a query on this to get the last line of text within this field.
Aside from records where this memo field is filled in I also have records where the memo field is blank. So basically if the memo field is blank I want a blank field returned as well as fields with data.
I am using Access 2003 but also am running this on Access 2000 (not sure if there has been a change to function names)
This is seriously bugging me - can anyone put me out of my misery?
I have a database that prints out a contract of line items (as a report) for the home remodeling business.
I use a query to limit the line items which show the description and cost as a subreport.
The only way I could create a total of these line items was to enter an unbound text control, and put in the formula =sum([cost]) where cost being the field name. It works fine, but I need to but the result in a currency format - - two decimals and a $ sign. I sure would like it to do it at the query level, instead of at the report level (will make it more user friendly)
how to make a line break in a rich textbox in access 2007? Chr(13) & Chr(10) worked until I changed the textbox to rich, which is necessary because certain words in the textbox need to be bold.
I'm a noobie with Access, and on my form which displays Account information, I also want to display the multiple Addresses this account can have. I can't find any type of scrollable text box (something to look like a spreadsheet), and I know it's not a list box I need to use.
How do I create this and what is it called? I tried using a data grid but I dont think that is it either :confused:
Also, my scrolling text icon on the toolbox is greyed out, not sure why.
I am creating a database and need to produce a report that will form an itemised quotation.There can be up to 8 items on the quotation along with their associated costs to the customer. I am trying to make the "item" field dynamically expand if necessary when longer text is entered into it and then expand the corresponding "Totals" fields to match the new size.I have tried using the "Can Grow" property however for some reason the formatting does not cascade down to the fields below and the "Totals" fields remain the same size.
To try and explain better I have included a screen shot of the relevant part of the report and how it looks when printed. I should point out that the "QuotesQuote" box at the top of the screen is set to "Can Grow" and when that expands the boxes below automatically move to accommodate this.
They are receiving e-mail with some data that they want to transfer to access database to track. The one think they can do is copy past, copy paste. That is a lot of data to copy and paste. The date looks like this format
Field1 Text1
Field2 Text2 (text2 can be more than 255 char)
Field3 Text3 (text3 can be more than 255 char)
Field4 Text4
Field1 Text1
Field2 Text2 (text2 can be more than 255 char)
Field3 Text3 (text3 can be more than 255 char)
Field4 Text4
and so on.. It can be 50 records
I am thinking they can copy this to the text file. Then the code form Access grabs the text file and imports to access table with format below.
I'm trying to import a pipe delimited text file into a table. I can import the entire table using the following code, but I only get one column of data (the entire data set in one column). If possible I would like to import with the columns defined or if not possible use some code for a function similar to text to columns.
We have a function that will allow a user to navigate to a text file and import it to a database. I've been asked to make it import ALL the text files from all sub-directories in the directory IF they've never been imported before (based on FileName and LastModifiedDate). I've set up a table to track what's getting imported (tbl_ImportHistory). I'm thinking what I need to do is use the previous coder's GetFileName function in a recursive loop removing the navigation request and adding the comparison and then, if it imports the file, the name and date need to be added to the tbl_ImportHistory table. But I'm completely unsure of how to do it.
I'm posting the GetFileName function below. I believe once I've got it modified the rest of the code can be left intact as it just parse's and loads whatever file is selected in the GetFileName function.
<code> Function GetFileName() As String Dim fd As FileDialog Set fd = Application.FileDialog(msoFileDialogFilePicker) fd.AllowMultiSelect = False Dim vrtSelectedItem As Variant
