Import z pliku txt z błędem.

0

Hej,

mam tabele która posiada różne kolumny z różnymi typami danych. Kolumn jest ok 22.
Teraz mam taki problem, że plik txt czasem posiada więcej niż 22 pola np. jeden wiersz na 1000 a reszta ma 22 znaki.
Kiedy taki plik posiada jeden wiersz np. ze znakami więcej niż 22 to następuje błąd importu. Wiersz ma np. wtedy ok 40 znaków lub np. 42 znaki itd.
Nie mogę chociażby rozszerzyć tabeli do bezpiecznej ilości kolumn bo typy danych są różne i wtedy nie ma zgodności.

Czy jest szansa aby podczas importu bulk import pomijał ten zbyt długi wiersz? Ewentualnie wycinał i wrzucał do pomocniczej tabeli, cokolwiek o by pozwoliło na pełny import?

Jak to nagryźć?

Pozdr!

0
  1. Stwórz procedurę do której przekażesz parametry i w procedurze zrób sobie
insert into tabla (pole1) values (SUBSTRING ( nazwa_pola,1, 22))
  1. Jeśli nie możesz zrobić procedury to utwórz tiggera, który zrobi to samo
  2. sprawdzaj długość stringa przed insertem i np pomijaj ten rekord
0
woolfik napisał(a):
  1. Stwórz procedurę do której przekażesz parametry i w procedurze zrób sobie
insert into tabla (pole1) values (SUBSTRING ( nazwa_pola,1, 22))
  1. Jeśli nie możesz zrobić procedury to utwórz tiggera, który zrobi to samo
  2. sprawdzaj długość stringa przed insertem i np pomijaj ten rekord

ad 1 - problem w tym, że zaczytałem cały plik tekstowy do zmiennej @z VARCHAR(MAX) za pomocą openrowset i nie wiem za bardzo, w którym momencie teraz wprowadzić Twój pomysł? Plik tekstowy zawiera oddzielone przecinkiem wartości

SET @sql = 'select @text_file=(select * from openrowset (
bulk ''' + @file + '''
,SINGLE_CLOB) x
)'

0

Plik tekstowy wygląda np.:

Owoc,1,2,3,4,5,6,7,8,9
Owoc,1,2,3,4,5,6,7,8,9
Owoc,1,2,3,4,5,6,7,8,9
**Owoc,1,2,3,4,**Owoc,1,2,3,4,5,6,7,8,9
Owoc,1,2,3,4,5,6,7,8,9
Owoc,1,2,3,4,5,6,7,8,9
Owoc,1,2,3,4,5,6,7,8,9

0

Ten podkreślony fragment jest błędny więc muszę go usunąć i importować tylko poprawne wiersze do tabeli.

0

Ja polecałbym jednak podejść do sprawy trochę bardziej metodycznie, do zadanego pliku stwórz odpowiednie schema.ini https://msdn.microsoft.com/en-us/library/windows/desktop/ms709353(v=vs.85).aspx

I wgraj

insert into tabeladocelowa
SELECT * 
FROM OPENROWSET(
    'Microsoft.ACE.OLEDB.12.0', 
    'Text; HDR=YES; Database=C:\Text', 
    'SELECT * FROM plik.csv') c
where
    --warunki eliminujące błędne wiersze
0

Tabela ma kolumn tyle aby zaimportować do niej nie więcej niż Owoc,1,2,3,4,5,6,7,8,9

0

czyli nie napisałem może ważnej informacji, że czasem poprawny wiersz się urywa i doklejany jest poprawny wiersz w jednej linii. Potem kolejne wiersze idą znowu ok. itd. itd

0

No to zrób inaczej.
Stwórz sobie tabelę w pamięci:

declare @MyTmpTable Table (pole1 varchar(max));

zaimportuj dane do tej tabeli tymczasowej, a następnie zrób to co podałem w pkt1

poradzisz sobie czy podać Ci konkretniej o co mi chodzi?

0

do zmiennej już wrzuciłem ale problem pojawia się w momencie zdefiniowania warunków dotyczących usunięcia fragmentu wiersza kiedy to w jednej linii pojawia się dwa razy słowo Corp

0

Rozmawiamy jak ślepy o kolorach...
Reasumując

  1. Pobrałeś wiersz do zmiennej
  2. w wierszu jest 2x corp

co nam przekazałeś:

  1. Masz csv do pobrania
  2. csv bywa spaczony

Czego nie wiemy:

  1. jakie dane są w csv
  2. dlaczego podwojne wystąpienie corp jest błędne
  3. Jak filtrujesz błędne rekordy

Mam pytanie, postaw się w roli pomagających odrzuć co wiesz na temat tego importu i bazuj tylko na informacjach przez siebie oczekiwanych.
Jakich rad udzielił byś pytającemu? o jakie dodatkowe info byś poprosił?

0

sorry ale przeskakuje po wątkach

teraz podpowiedz mi jak przenieść ze zmiennej tablicowej do której wrzuciłem tekst jako varchar jednym ciągiem do tabeli tak aby rozdzielić na kolumny odpowiednio te wartości

0

w t-sql?

0

tak, bo mam już zaczytane z pliku pozycje, które mogę zwrócić poprzez zapytanie: select *from @rows
gdzie wcześniej poszła deklaracja:

    declare @rows table (
id int identity(1, 1),
    row varchar(8000)

);

Teraz mam tabele gotową ok 30 kolumn i do niej potrzebuje powrzucać kolejno dane ze zmiennej @rows

0

Jak już pisalem warto przysiąść i zrobić prawidlowe schema.ini, teraz pozostaje rzeźbienie substringami lub szukanie split (http://stackoverflow.com/questions/697519/split-function-equivalent-in-t-sql) lub wbudowanej od wersji 2016: string_split (https://msdn.microsoft.com/en-us/library/mt684588.aspx).
Co kto lubi, nie ma drogi na skróty, albo na początku dzielisz na kolumny albo w trakcie.

Ja wychodzę z założenia, że lepiej wykorzystać własciwości csv niż wymyślac koło na nowo....

0

No przecież Ci pisałem ...

insert into tabela (pole1, pole2, ...., polen) 
(select substring(r.row,1,20), substring(r.row,21,40), ..., substring(r.row,n,n+40) from @rows r) 
0
Panczo napisał(a):

Jak już pisalem warto przysiąść i zrobić prawidlowe schema.ini, teraz pozostaje rzeźbienie substringami lub szukanie split (http://stackoverflow.com/questions/697519/split-function-equivalent-in-t-sql) lub wbudowanej od wersji 2016: string_split (https://msdn.microsoft.com/en-us/library/mt684588.aspx).
Co kto lubi, nie ma drogi na skróty, albo na początku dzielisz na kolumny albo w trakcie.

Ja wychodzę z założenia, że lepiej wykorzystać własciwości csv niż wymyślac koło na nowo....

Czy możesz bliżej opisać co masz na myśli przez właściwości csv - miałem trochę do czynienia z tym formatem ale jak on miałby mi ułatwić pracę ?

0

Każdy csv można opisać i zdefiniowac jakie dane posiada. Ogólne właściwości to np.:kodowanie znaków, separator pol, kwalifikator pol tekstowych, format daty, separator dziesiętny, czy pierwsz wiersz posiada naglowki itd

Dodatkowo możesz zdefiniować kolumny co do typu i nazwy.

W ms masz drivery do pobierania danych z csv i te informacje definiujesz w pliku schema.ini. W efekcie zasysasz dane jak z tabeli.

Czytanie pliku wiersz po wierszu i obrabianie w celu pobrania konkretnych pól to dla mnie wymyślanie kola na nowo

0

Czy znasz jakieś dobre kompendium wiedzy o tym jak importować, eksportować do csv?
Jak poprawnie definiować same csv i schemat.ini to nich?

0

Słowem wstępu, odkąd pracuje i spotykam się z tym, że ktoś pobiera dane z csv to mam doczynienia z masą kodu parsującego linie i wyciągającego poszczególne pola. Zupełnie jakby CSV znaczyło: zrób to sobie sam.

W środowisku MS mamy przecież Text Driver, który potrafi znacząco ułatwić pracę z tymi plikami. Wystarczy poprawnie zdefiniować schema.ini i wszystko gotowe. Szczegóły tutaj: https://msdn.microsoft.com/en-us/library/ms709353(v=vs.85).aspx

Jeden obraz jest wart więcej niż tysiąc słów, więc zobrazujmy to: zadanie zaimportować plik csv:

test.csv

125.5,21-01-2017,"jakis tam tekst z przecinkami, aby było trudnie."
tutaj jakis błędny wiersz
12,22-01-2017,"jakis tam tekst z przecinkami, aby było trudnie"

To co ja proponuje w tym poście to opracowanie schema.ini i w prosty sposób zaimportowanie do tabeli.
Zbierzmy zatem informacje o danych wejściowych:

  • Pola rozdzielane są przecinkami
  • Brak nazw kolumn w 1 wierszu
  • W pierwszej kolumnie mam kwotę/liczbę z częściami dziesiętnymi rozdzielonym kropką
  • Druga kolumna to data w formacie dd-mm-yyyy
  • W trzeciej kolumnie jest tekst "otoczony" znakiem "
  • Nie wszystkie wiersze są poprawne

No to definiujemy schema.ini

[test.csv]
Format=CSVDelimited
ColNameHeader=False
DecimalSymbol=.
DateTimeFormat=DD-MM-YYYY
TextDelimiter="

Col1=Kwota Currency 
Col2=Data DateTime 
Col3=Opis Text

Pobieramy dane w SQL

SELECT 
	kwota,
	data,
	Opis
FROM
	OPENROWSET ('MSDASQL'
				,'Driver={Microsoft Access Text Driver (*.txt, *.csv)}; 
				  DBQ=e:\csv;'
				,'SELECT * from "test.csv"') dt

Wynik:

Kwota Data Opis
125,50 2017-01-21 0000.0000000 jakis tam tekst z przecinkami, aby było trudnie.
NULL NULL NULL
12,00 2017-01-22 0000.0000000 jakis tam tekst z przecinkami, aby było trudnie

W efekcie otrzymałem tabelę:

  • z nazwami kolumn
  • poprawnymi typami danych
  • z możliwością filtrowania za pomocą WHERE

Oczywiście muszę się pozbyć błędnych rekordów, ale całość może wyglądać tak:

INSERT INTO tabeladocelowa
SELECT 
	kwota,
	data,
	Opis
FROM
	OPENROWSET ('MSDASQL'
				,'Driver={Microsoft Access Text Driver (*.txt, *.csv)}; 
				  DBQ=e:\csv;'
				,'SELECT * from "test.csv"') dt
WHERE
	not Opis  is null;

I teraz zainteresowani niech porównają ile pracy kosztuje zdefiniowanie poprawnego schema.ini, a ile trzeba zrobić aby to "ręcznie" parsować...

0
Panczo napisał(a):

Słowem wstępu, odkąd pracuje i spotykam się z tym, że ktoś pobiera dane z csv to mam doczynienia z masą kodu parsującego linie i wyciągającego poszczególne pola. Zupełnie jakby CSV znaczyło: zrób to sobie sam.

W środowisku MS mamy przecież Text Driver, który potrafi znacząco ułatwić pracę z tymi plikami. Wystarczy poprawnie zdefiniować schema.ini i wszystko gotowe. Szczegóły tutaj: https://msdn.microsoft.com/en-us/library/ms709353(v=vs.85).aspx

Jeden obraz jest wart więcej niż tysiąc słów, więc zobrazujmy to: zadanie zaimportować plik csv:

test.csv

125.5,21-01-2017,"jakis tam tekst z przecinkami, aby było trudnie."
tutaj jakis błędny wiersz
12,22-01-2017,"jakis tam tekst z przecinkami, aby było trudnie"

To co ja proponuje w tym poście to opracowanie schema.ini i w prosty sposób zaimportowanie do tabeli.
Zbierzmy zatem informacje o danych wejściowych:

  • Pola rozdzielane są przecinkami
  • Brak nazw kolumn w 1 wierszu
  • W pierwszej kolumnie mam kwotę/liczbę z częściami dziesiętnymi rozdzielonym kropką
  • Druga kolumna to data w formacie dd-mm-yyyy
  • W trzeciej kolumnie jest tekst "otoczony" znakiem "
  • Nie wszystkie wiersze są poprawne

No to definiujemy schema.ini

[test.csv]
Format=CSVDelimited
ColNameHeader=False
DecimalSymbol=.
DateTimeFormat=DD-MM-YYYY
TextDelimiter="

Col1=Kwota Currency 
Col2=Data DateTime 
Col3=Opis Text

Pobieramy dane w SQL

SELECT 
	kwota,
	data,
	Opis
FROM
	OPENROWSET ('MSDASQL'
				,'Driver={Microsoft Access Text Driver (*.txt, *.csv)}; 
				  DBQ=e:\csv;'
				,'SELECT * from "test.csv"') dt

Wynik:

Kwota Data Opis
125,50 2017-01-21 0000.0000000 jakis tam tekst z przecinkami, aby było trudnie.
NULL NULL NULL
12,00 2017-01-22 0000.0000000 jakis tam tekst z przecinkami, aby było trudnie

W efekcie otrzymałem tabelę:

  • z nazwami kolumn
  • poprawnymi typami danych
  • z możliwością filtrowania za pomocą WHERE

Oczywiście muszę się pozbyć błędnych rekordów, ale całość może wyglądać tak:

INSERT INTO tabeladocelowa
SELECT 
	kwota,
	data,
	Opis
FROM
	OPENROWSET ('MSDASQL'
				,'Driver={Microsoft Access Text Driver (*.txt, *.csv)}; 
				  DBQ=e:\csv;'
				,'SELECT * from "test.csv"') dt
WHERE
	not Opis  is null;

I teraz zainteresowani niech porównają ile pracy kosztuje zdefiniowanie poprawnego schema.ini, a ile trzeba zrobić aby to "ręcznie" parsować...

No to zacząłem test tego co napisałeś. Szukałem i goglowałem i nadal to samo, - używam Microsoft SQL Server 2014 - 12.0.2269.0 (X64)
Jun 10 2015 0345
Copyright (c) Microsoft Corporation
Express Edition (64-bit) on Windows NT 6.3 <X64> (Build 14393: )

Error przy zapytaniu:
SELECT
    kwota,
    DATA,
    Opis
FROM
    OPENROWSET ('MSDASQL'
                ,'Driver={Microsoft Access Text Driver (*.txt, *.csv)};
                  DBQ=D:\Piotrek\CSV\4programmers\csv;'
                ,'SELECT * from "test.csv"') dt

OLE DB provider "MSDASQL" for linked server "(null)" returned message "[Microsoft][Menedżer sterowników ODBC] Nie można odnaleźć nazwy źródła danych, a nie ma podanego sterownika domyślnego".
Msg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of OLE DB provider "MSDASQL" for linked server "(null)".

0

Musisz mieć zainstalowany sterownik ODBC. Doinstaluj wersje 64 bit Microsoft Access 2010 engine http://www.microsoft.com/downloads/details.aspx?FamilyID=C06B8369-60DD-4B64-A44B-84B371EDE16D&displaylang=en

0

program zainstalowany wg linku, jednak zażądał instalacji 32 bit wersji wiec taką zainstalowałem, restart sql też nie pomógł - może jakaś konfiguracja bo komunikat nadal ten sam?

OLE DB provider "MSDASQL" for linked server "(null)" returned message "[Microsoft][Menedżer sterowników ODBC] Nie można odnaleźć nazwy źródła danych, a nie ma podanego sterownika domyślnego".
Msg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of OLE DB provider "MSDASQL" for linked server "(null)".

0

Sterowniki muszą być 64 bitowe, bo taki masz SQL Server, instalujesz na serwerze SQL.

0

A i jeszcze mnie naszło, katalog: D:\Piotrek\CSV\4programmers\csv jest na serwerze?
Generalnie dla zasady napiszę:
Sterowniki i pliki csv i schema.ini muszą być na serwerze, OPENROWSET korzysta z zasobów serwera na którym się znajduje, mimo, że zapytanie puszczasz z innej końcówki via SSMS

0

mam office zainstalowany w wersji 32 bit stad 64 nie mogę
screenshot-20170122165141.png

0

serwer SQL mam lokalnie na laptopie i wszystkie pliki, kurcze musze odinstalowc office w wersji 32 bit aby móc zainstalowac ten dodatek , nie za dobrze w takim razie nie ma obejscia?

0

sciagne wersje sql 32 bit i sprawdze

0

Teraz poszło, musiałem jeszcze ustawić:

EXEC sp_configure 'show advanced options', 1
RECONFIGURE
GO
EXEC sp_configure 'ad hoc distributed queries', 1
RECONFIGURE
GO

1 użytkowników online, w tym zalogowanych: 0, gości: 1