Search results for query: Imex

...you can start on a different row if required, just specify the range SELECT * FROM (SELECT * FROM [sheet1$B:D] AS xlData IN 'C:\path\filename.XLSX'[Excel 12.0;HDR=Yes;IMEX=0;ACCDB=Yes]) AS XL; or SELECT * FROM [excel 12.0 xml;hdr=yes;imex=1;DATABASE=C:\Path\filename.XLSX].[Sheet1$B:D] AS XL;

what does that mean exactly? if if this is a csv file, perhaps try my suggestion in post#14 - no need to provide a DSN, IMEX or characterset

...using sql rather than a linked table. Not only can you connect to the excel file regardless of whether it is open or not, but with the right imex setting you can edit excel from access and the changes are immediately visible in excel. Editing is limited, you can’t delete a row for...

...than transferspreadsheet - something like SELECT * FROM (SELECT * FROM [sheet1$A:D] AS xlData IN 'C:\path\filename.XLSX'[Excel 12.0;HDR=no;IMEX=1;ACCDB=Yes]) AS XL; or for a full sheet SELECT * FROM [excel 12.0 xml;hdr=yes;imex=1;DATABASE=C:\path\filename.XLSX].[Sheet1$A:D] AS T; both...

No that won't work - you would still need to alias if you set HDR=no;IMEX=0; you still get the headers changing IMEX to 2 will put the headers in the first row of data - but of course you can't edit it.

..."FROM [All Deed of Trust Secured Loans]; " with "FROM (SELECT * FROM [sheet1$A:D] AS xlData IN 'C:\Path\filename.XLSX'[Excel 12.0;HDR=Yes;IMEX=0;ACCDB=Yes]) AS [All Deed of Trust Secured Loans]; " replace sheet1$:A:D and C:\Path\filename with the appropriate values. which may speed up...

...Department\Flood Insurance\Flood Vendors\ServiceLink\Flood Revision Notice Reports\All_Deed_of_Trust_Secured_Loans.xlsx'[Excel 12.0;HDR=Yes;IMEX=0;ACCDB=Yes]) AS [All Deed of Trust Secured Loans]; " to "SELECT * FROM (SELECT * FROM [All_Deed_of_Trust_Secured_Loans$A:AL] AS xlData IN...

...1 example SELECT XL.* FROM (SELECT * FROM [Sheet1$] AS xlData IN 'G:\MyFiles\ExampleDatabases\Excel\tblData.xlsx’ [Excel 12.0;HDR=yes;IMEX=0;ACCDB=Yes]) AS XL; I don't know the column headers but as an example you might have SELECT XL.* FROM (SELECT hdr1 as newheader, hdr2 as...

...then exclude the first row and convert values as required. INSERT INTO DBTable( ArticleNo, [FieldList] ) SELECT ArticleNo, [FieldList] FROM (SELECT * FROM [sheet1$] AS xlData IN 'D:\AnyDirectory\AnyFile.xlsx'[Excel 12.0;HDR=No;IMEX=2;ACCDB=Yes] where F1<>"ArticleNo")...

...use the external data tab to link to the file once. This will create a connection string in the msysobjects table. It also populates the msysIMEXSpecs and msysIMEXColumns tables which is where the actual spec is stored - but no need to worry about that. Connect Database Text;DSN=Testtab...

I've not used sharepoint so wasn't aware it is used there. With excel, setting IMEX=0 enables access to update excel directly using sql or by manually editing the query (at least as a dao recordset, not tried it with ado) but for the purposes of importing data, doesn't really matter which one...

...fName = "D:\Dev\ltexttest.XLSX" db.Execute ("UPDATE (SELECT * FROM [sheet1$A:B] AS xlData IN '" & fName & "'[Excel 12.0;HDR=yes;IMEX=0;ACCDB=Yes]) AS XL SET XL.test = '', XL.dummy = ''", dbFailOnErrror) db.Close If Not IsWorkBookOpen(fName) Then 'file not open, so open it...

Agreed - but those two IMEX tables need to be created by Access. Create them manually or using VBA does not 'register' them with Access. They can only be created by creating an import specification. Once created, they can be edited if you know what you are doing. Yes, you can include them when...

another way is to use a linked query - something like INSERT INTO tblDestination ( field1, field2, field3 ) SELECT field1, field2, field3 FROM (SELECT * FROM [sheet1$] AS xlData IN 'C:\pathtofile\XLfile.XLSX'[Excel 12.0;HDR=yes;IMEX=2;ACCDB=Yes]) AS XL;

...the sql window, not VBA best guess it would be SELECT * FROM (SELECT * FROM [Text;DSN=Volksbank Import Spezification;FMT=Delimited;HDR=yes;IMEX=2;CharacterSet=850;DATABASE=C:\Users\Albert\Documents\AJL\CSV_Daten\Test\MyTestFiles\].[TestDaten.csv ]) AS T or my version SELECT * FROM...

...* FROM [TEXT;DATABASE=Y:\BUSINESS\Data\2021\;HDR=Yes].OktbisDez2021.csv I always alias it as sometimes you can get issues SELECT * FROM (SELECT * FROM [TEXT;DATABASE=Y:\BUSINESS\Data\2021\;HDR=Yes].OktbisDez2021.csv) AS txt; IMEX relates to excel files so not sure why it has been included

...like this as a select query, just modify to become an insert query SELECT XL.* FROM (SELECT * FROM [sheet1$] AS xlData IN 'D:\Path\filename.xlsx'[Excel 12.0;HDR=yes;IMEX=0;ACCDB=Yes]) AS XL; change sheet1$ as required - can also reference ranges [sheet1$A1:B25] [sheet1$C50:F85] etc

...from dynaset to snapshot. then save the query. Or perhaps do this in your form instead - that might even work for a linked table Note that IMEX has nothing to do with file locking, only which datatype a column of data will be interpreted as based on the first 8 rows. My point was that with...

...for the mention - probably worth pointing out that you also can't delete a record (row) in excel from access. @Isaac - I agree, you can use imex=1 (import mode) to treat everything as text rather a datatype determined in the first few rows - in principle anyway. If your first 8 rows have a...

that's why I prefer to use csv or tab delimited files - you know exactly what you are dealing with and can plan accordingly. with regards imex=1, still won't solve the problem if the first 8 rows are numeric and text is further down. I get round the problem by importing without headers and imex...