Temat: import z xlsx (pola powyżej 255 znakaów)

Cześć,

Mam problem z importem do tabeli Access pliku Excel (xlsx). Plik ma w tej chwili 180 kolumn i ok. 9000 wierszy.
W niektórych kolumnach ilość znaków w komórce grubo przekracza 255. Podczas importu takie komórki są obcinane - do tabeli trafia pierwsze 255 znaków.
Tabela w Accesss ma dwa rodzaje pól: tekstowe oraz Memo (dla tych kolumn, gdzie spodziewam się dłuższych ciągów tekstowych).

Próbowałem:
1. Zwykłym insertem -> "INSERT INTO [Table] (" & strTemp1 & ") SELECT * FROM [Excel 12.0;HDR=YES;DATABASE=" & strTemp_SourcePath & "].[Sheet_Name$]"
2. DoCmd.TransferSpreadsheet
3. Pętlą po ADO recordset

Jak długo obrabiam plik po stronie VBA, tak długo widzę wszystkie znaki powyżej 255. Jednakże z chwilą próby załadowania ich do tabeli (recordset'a) wszystko się rypie…
Próbowałem dodawać dodatkowe wiersze na początku pliku, które w kolumnach mających być rozpoznane jak MEMO mają stringi 300-znakówe ale nadal nic.
Próbowałem zamienić xlsx na csv ale z uwagi na znaki podziału wiersza itp., csv poprawnie wyświetla się tylko w Excel. Nie nadaje się jako źródło danych do importu.

Znacie jakiś chytry myk?

Temat: import z xlsx (pola powyżej 255 znakaów)

Cześć,

Przed importem - ale w Excelu posortuj importowaną tabelę excelowską malejąco po kolumnie, w której liczysz długość ciągu (i zapisz plik).

Efekt ma być taki - że pierwszy importowany rekord z Excela ma zawierać wartość dłuższą niż 255 znaków (Access oceniając ile znaków ma być importowanych bierze pod uwagę tylko kilka pierwszych rekordów).

Po powyższym DoCmd.TransferSpreadsheet sobie poradzi.

Pozdrawiam!

Temat: import z xlsx (pola powyżej 255 znakaów)

Tomasz G.:
Przed importem - ale w Excelu posortuj importowaną tabelę excelowską malejąco po kolumnie, w której liczysz długość ciągu (i zapisz plik).

Nie bałdzo :(

Jak pisałem wcześniej, wstawiłem do pliku 10 pierwszych wierszy z tekstem 300 znakowym w kolumnach w których spodziewam się dłuższych ciągów (i które jednocześnie w tabeli Access są zadeklarowane jako MEMO) ale nic to nie dało.

Poza tym - ponieważ jest to kilkanaście kolumn, to sortowanie po jednej i tak nie rozwiąże problemu pozostałych, w których akurat w pierwszych paru rekordach może być mniej niż 255 znakjów.Ten post został edytowany przez Autora dnia 11.08.18 o godzinie 10:48

Temat: import z xlsx (pola powyżej 255 znakaów)

Swego czasu miałem problem z "gubieniem" przez Access kolumn występujących w arkuszu podczas importu. Wpadłem na pomysł aby przed połączeniem się z plikiem xlsx z wszystkich danych w arkuszu zrobić tabelę (CTRL+T) z nagłówkami.

Może i u Ciebie może to zadziała.

Dorzucam funkcję, którą automatycznie tworze tabele dla plików w pętli.

Function CreateTableInExcelFile(SourceFile As String, Optional Sheet As Variant) As Boolean

    Dim ApXL As Object

    Dim xlWbk As Object

    Dim xlWsh As Object

    Dim xlRange As Range

    Dim LastRow As Long

    Dim LastColumn As Long

    

    Set ApXL = CreateObject("Excel.Application")

    

    ApXL.Visible = False

    

    Set xlWbk = ApXL.Workbooks.Open(SourceFile)

    Set xlWsh = xlWbk.ActiveSheet

    

    LastRow = xlWsh.Cells.SpecialCells(xlCellTypeLastCell).Row

    LastColumn = xlWsh.UsedRange.Columns(xlWsh.UsedRange.Columns.Count).Column

    

    'On Error Resume Next

    With xlWsh

        .Activate

        .ListObjects.Add(xlSrcRange, xlWsh.Range(xlWsh.Cells(1, 1), xlWsh.Cells(LastRow, LastColumn)), , xlYes).Name = "Table1"

    End With

 

    With xlWbk

        .CheckCompatibility = False

        .Save

        .CheckCompatibility = True

        .Close

    End With

    ApXL.Quit

    Set xlWsh = Nothing

    Set xlWbk = Nothing

    Set ApXL = Nothing

    

    CreateTableInExcelFile = True

End Function

Temat: import z xlsx (pola powyżej 255 znakaów)

Bartłomiej D.:

Tomasz G.:
Przed importem - ale w Excelu posortuj importowaną tabelę excelowską malejąco po kolumnie, w której liczysz długość ciągu (i zapisz plik).

Nie bałdzo :(

Jak pisałem wcześniej, wstawiłem do pliku 10 pierwszych wierszy z tekstem 300 znakowym w kolumnach w których spodziewam się dłuższych ciągów (i które jednocześnie w tabeli Access są zadeklarowane jako MEMO) ale nic to nie dało.

Poza tym - ponieważ jest to kilkanaście kolumn, to sortowanie po jednej i tak nie rozwiąże problemu pozostałych, w których akurat w pierwszych paru rekordach może być mniej niż 255 znakjów.

Spokojnie. Nie ma powodu jeszcze do smutnej miny. Nie takie rzeczy TransferSpreadsheet potrafi.

Pod linkiem: http://pliki.itschool.pl/acc.zip
Paczka z plikiem Excela i baza Accessa, a w niej dwa makra importujące dane do dwóch różnych tabel z jedną linią kodu każde:
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "Tabela1", "C:\Users\admin\Desktop\acc\DaneExcel1.xlsx", True

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "Tabela2", "C:\Users\admin\Desktop\acc\DaneExcel1.xlsx", True

Rozumiem, że u Ciebie w bazie efekt jest jak w przypadku makra dla Tabeli1?

Czy w takim układzie -ale bez rzutu okiem na bazę to strzał - masz w swojej tabeli pola Nota (Memo) ale z formatem @ (to zjawisko powstaje gdy tabela powstała z importu danych które Access zinterpretował jako Tekst a nie Nota - wtedy nawet po zmianie w Accessie Tekstu na Notę małpka w Formacie zostaje)? - jeżeli tak- usuń format ustawiając go na wartość pustą.

Po tej czynności przy formacie pustym/niezdefiniowanym dane importowane (w kolejnych importach) dłuższe niż 255 znaków będą prawidłowo umieszczane w tabeli.

Temat: import z xlsx (pola powyżej 255 znakaów)

Krzysztof B.:
Swego czasu miałem problem z "gubieniem" przez Access kolumn występujących w arkuszu podczas importu. Wpadłem na pomysł aby przed połączeniem się z plikiem xlsx z wszystkich danych w arkuszu zrobić tabelę (CTRL+T) z nagłówkami.

Może i u Ciebie może to zadziała.

No niestety - po zmianie zakresu na tabelę nadal importuje tylko pierwsze 255 znaków.

Temat: import z xlsx (pola powyżej 255 znakaów)

Tomasz G.:
Spokojnie. Nie ma powodu jeszcze do smutnej miny. Nie takie rzeczy TransferSpreadsheet potrafi.

Zaczynam widzieć światełko w tunelu :)
Co prawda w załączonej przez Ciebie bazie, po wykonaniu obu makr, otrzymałem wartości obcięte do 255 znaków ale może to wynikać z faktu, że string 258-znakowy jest dopiero w 50 wierszu, więc Access zakwalifikował wszystko jako tekst.

Próbowałem wcześniej usuwać format "@" ze wszystkich pól MEMO w bazie ale bez rezultatu (przynajmniej tak mi się wydawało). Wygląda na to, że coś pokręciłem.
Ćwiczenie powtórzyłem. Dodatkowo do importowanego pliku xlsx wstawiłem 10 pierwszych wierszy z wartościami o długości 300 znaków.
Odpaliłem TransferSpreadsheet i wygląda na to, że poszło. Co prawda boczy mi się na jedną z kolumn z datą ale to już mniejszy problem.

Testuję to i sprawdzam czy na pewno wszystko hula ale jestem dobrej myśli. Możliwe, że teraz trafię na problem odwrotny - po zwróceniu danych z Access do Excel też będzie obcinać ale to już inna bajka i problem na przyszłość :)

Dzięki!