Import CSV File From Database PRO To Access: Unusual Record Splitting Problem
Jul 19, 2006
Hello everyone,
I'm trying to import data from our current Database Pro v1.0 DB to an Access DB that I'm creating.
Our DBPRO is essentially a flat-file data entry program. It has a "subform" for history events that isn't actually in it's own table, but all concatenated in a single [History] field.
Basically, when viewed in DBPRO, it's broken into different records, yet it's actually stored as one. DBPRO uses °, ±, □, and 0's to separate the different "fields", but Access can't seem to break it down automatically.
When I export the data to a CSV file, everything else comes through with minimal problems. The [History] field, of course, comes in as a huge block of concatenated records.
I've attached an example of this below. I included only the field in question, ([History]), and the primary key, ([Last Name/Cust]). The first tab in my example is a single record, recently imported. The second tab shows how I need it to be, broken into multiple records.
Is there anyway I can split these records, while maintaining the primary key? It's my goal to have all the other information in one table, and the history records in a separate one.
Thanks so much for your help! I've researched all over, and just can't seem to find a similar problem, or solution. :(
I'd like to import an excel file but the data begin from cell "A10", above there is a "privacy text".Is it possibile import or link the excel data in an access table directly from the cell A10?
Someone decided to be clever and send a text file with a stupid date format, is it possible to show off a little here and get a working date format, it's a text file I am working with: Dec 18 2014 01:12PM.
Not that is an exactly cut/paste so spaces are as they are here, ideally would like to get 18/12/14 13:12.
Hi all, I'm trying to get the following done: I have code which import Excel files into my database with the "DoCmd.TransferSpreadsheet". which works great. But I'm now splitting the database and I want to have the Excel files which a user selects in his frontend database, imported in my backend database. I don't see the possibility in the TransferSpreadsheet as this is set to the CurrentDb. My temporary solution is to import in the frontend and CopyObject to the Backend, but does anyone have a direct solution?
I have a secure MS Access database, where users by default can only read data, but not write. How can I prevent them from importing or linking files, or inserting objects? I don't want to use any passwords in my database, since it's used inside the application. Thank you.
I have created a db for a nonprofit counseling org. I had created the first half, mostly administrative tasks, called Phase1, and put the BE on a network drive and the FE on multiple users. Now in developing the clinical portion, Phase2, I linked to four of the tables in Phase1 BE file.
Now I tried to split Phase2 the same way as Phase1 and got an error "Subscript out of Range". I think because of already having some external links in it. I checked for the file and Access actually created the BE file for the new phase2, all of the tables are in it minus the four I was linking to. But, access didn't create a FE file. The original still has all of the objects, tables and forms etc. My question is; can I link to the tables in the new BE file even though those tables still exist in the original file?
I have created a database with approximately 30 columns, 1st column being a primary key. At the end of the database there are 5 columns. We will call them VWXYZ.
One of my users wants to be able to update VWXYZ via importing an Excel document to the existing table. He does NOT want fields 1-25 updated. He is editing his Excel document to only include the primary key and then VWXYZ with the same exact column names. When he imports, it sets 2-25 to blank and updates VWXYZ to the updated information.
How can he import an Excel document that only updates VWXYZ?
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
I am attempting to split my Access Database and will upload the back-end portion to a SharePoint site. No matter what I do, I continue to get a "Not a valid file name" error.
OK. I feel like an idiot but I did read the manual, Googled, and Microsoft help, but still cannot do it. The problem is on the import feature, there is no option to choose an Excel file.
I lowered the macro security level to take it out of "sandbox" mode, I reinstalled office and selected run all features again. I updated as well.
I tried blank databases to import to. No luck. I go to external data, import and I can choose ODBC, XML, sharepoint or Access files only.
I am using MS Office Pro 2003. Thanks for the help.
Using MS 2007, I have a 200 text files exported each day from another application that has two different types of lines (see below). I would like to import each text file in to a database as a single record.
Text file example (text.txt):
R111 WC 8/21/2012 7:00 Doe, John doej 10110110
First Question? Y Second Question? N Third Question? Y ... Seventeenth Question? 10
As you see, I have the first row with multiple fields, but the next rows I have a question and an answer.
I would like to have this data imported as shown in the attachment. Example.zip
Most answers I see are for either multiple lines (same data and sizing). I am not sure how to handle several different lines with that vary in size and delimiters.
How to Import Txt file that dont have delimiters (Seprators) in Access DB.:confused: Actually iget this file from server...and every time i have to do it manaually. I need to automate this....please help thanks
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 need to import an excel file every week into Access. The file is always saved in the same folder called "Current" however the file name changes week to week because of a date and time stamp.
For example this week the file is named:
Weekly_Internet_Order_Matchup_Converted_Channel_Su mmary_20120721_080603 next week it will be Weekly_Internet_Order_Matchup_Converted_Channel_Su mmary_20120728_074452
Is there a way for me to import the file by ignoring everything after the "y"?
I need to import 15 csv files into access every month. I have location of all those files listed in a table in the same access DB but i'm not sure how to incorporate them in a macro I have written to import one of those files:
Function Macro_TransferCsvFileTo_RatesTradeData() On Error GoTo Macro_TransferCsvFileTo_RatesData_Err DoCmd.TransferText acImportDelim, "Import_Spec_tbl_RatesData", "tbl_RatesData", "c:Documents and SettingsaggauraDesktopWIPXMLDB_JOH_FI_2011123 0.csv", True, ""
[Code] ....
where c:Documents and SettingsaggauraDesktopWIPXMLDB_JOH_FI_2011123 0.csv is the file name I want to be replaced by each of the file listed by me in the other access table.
I've been able to find the code I need to import the file, but there are headers that come through from the group sending the excel file that will not import - they have a "." in them and that won't work. I need to find a way to remove the character and bring the excel header in line with the access table I'm importing to. I'm using the following to import the file:
Sub Example() 'the path to the excel workbook Dim strExcelPath As String strExcelPath = "C: est est esting.xls" 'import data from excel Call DoCmd.TransferSpreadsheet(acImport, _ acSpreadsheetTypeExcel8, "testtable", strExcelPath, _ True, "A1:AA11") End Sub
And that work fine up until it hits the offending headers. what is a good, quick bit of code to plug in to alter the headers and what, if any references would need to be added?
My problem concerns importing a date from a txt file. I've been doing this for years with '97 and 2003 with no problem, but with 2007 I am now getting import errors which I can't resolve. The files, either fixed width or delimited, contain date information in the format ddmmyyyy (without any date separators). After importing I receive an import error message, and the relevant field in the table is blank.
In Access '97 or 2003 I can import date information directly into an Access date/time field by using an import specification where I have set the date delimiter to blank (by default this is set to "/" , so I simply delete this to leave the box empty). Using this method a file containing "25122007" (without the inverted commas) will be imported into Access as a date, and can be subsequently manipulated as date without having to resort to any fancy reformatting tricks. Obviously the import will not be sucessful if the date delimiter in the import spec is left as "/", or set to anything else, eg "." or ":".
But now that I've updated to Access 2007 this is just not possible no matter what I do or try. I can only import a date if the txt data has a separator, and that the same separator is set in the import specification.
In some new databases I have done a couple of workarounds, importing it as text into a text field, then using a CDate(Format(CLng([date]),"00/00/0000")) type expression in a subsequent query, but this is a pain having to do it, a really backward step.
But the real pain is older databases ('97 or 2003) which have been converted up to '07; they no longer work, and there is a lot of redesign to be done unless somone can tell me what I'm doing wrong.
Unfortunately I am not able to change the format of the dates in the data I receive, so the solution has to come from my end (ie Access). Surely Access 2007 should be able to recognise 25122007 as a date? All ideas gratefully received.
I've been happily using various versions of Access since 1994 and I've learnt lots along the way, and have always previously been able to sort out problems that I've come across without resorting to a forum, but I'm now really puzzled by a problem that I have come across in Access 2007. So although this is my first post I certainly wouldn't call myself a newbie. Hopefully I have described the problem accurately (it is realy simple in fact), but very happy to provide more information if required.
I want a user to click a button, have the file open dialog open, they select a spreadsheet, and then it imports into a table. The problem is the filename can be different every time. The table name will remain constant.
Here is the OnClick:
Code: Private Sub Command8_Click() On Error GoTo Err_ImportSpreadsheet_Click DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel2Xml, "Table1", "T_Staff.xls", "Yes"
I'm new to Access and VBA, for the record. What I need to do is copy a SQL database (table structures and records) and save it as a local Access database, so that the user can query and make any changes on the local file without editting the SQL database.
Looking around, it seems like the best option would be to use the TransferDatabase method with the acImport option. Does this sound reasonable, or can someone with more experience suggest a better way to go about it? Thanks.
I'm trying to import a text file with 273 fields into two tables. I've been able to do this with the code I found on an old thread and I'm now trying to accomplish everything with one step. The file I'm importing is tab delimited text file. With this current code I'm only able to populate the first record in the table and then I get error message. (Run-time error '3265') (Item cannot be found in the collection corresponding to the reqested name or ordinal).
Code:
Public Sub ImportTextFile() ' to use the ADODB.Recordset, be sure you have a reference set to ADO Dim rst As ADODb.Recordset Dim rst2 As ADODb.Recordset Dim strFile As String Dim strInput As String Dim varSplit As Variant Dim intCount As Integer
I am trying to help a person that has a 1980s DOS database called "SMART". The program "smart" will output the database in a format that Access should be able to read, but it can't read the full file.
Any obvious ideas about what might be the problem?
