Import z pliku tekstowego (CSV)

Moduł konfiguracji programu StudioSystem posiada funkcjonalność pozwalającą na ustawienie parametrów do uniwersalnego importu danych do bazy SQL z plików w formacie tekstowym tzw. CSV . Pliki mogą mieć różną strukturę a dane rozdzielone mogą być różnymi symbolami, domyślnie znakiem przecinka, ale dopuszczalne są znaki średnik lub pionowa kreska.

Schematy importu danych

Import realizowany jest za pomocą dedykowanej transakcji tej samej co import plików XLS (role_sys/import_xls.aspx) jednak, aby import został wykonany należy odpowiednio skonfigurować schemat za pomocą którego dane będą pobierane.

Można wgrywać aplikacja w chmurze dowolne dane do dowolnych tabel na podstawie dowolnej liczby schematów importu danych z plików tekstowych, gdzie rekordy są zapisane jako wiersza, a kolumny rozdzielone znacznikiem.

UWAGA: ILOŚĆ KOLUMN W PLIKU CSV MUSI ODPOWIDAĆ ILOŚĆ ZDEFINIOWANCYH KOLUMN W SZABLONIE (dla plików excel nie jest to wymagane). Jeżeli chcemy pominąć kolumnę znajdującą się w pliku CSV wówczas definiujemy typ danych jako _ (podkreślenie)

W przykładach omówimy najczęściej wykorzystywane konfiguracje schematów importu kartotek magazynowych i kontrahentów oraz dokumenty zleceń wydań z magazynu. Za pomocą schematów można jednak przekazywać dowolne informacje w tym konfigurację oprogramowania pomiędzy różnymi instalacjami – bazami danych.

Schemat składa się z dwóch części zapisywanych w tabeli x_xls:

• Nagłówka – ogólne informacje dotyczące schematu
  • AKTYWNE – możliwość wyłączenia schematu do wyboru przez użytkownika.
  • NAZWA – nazwa schematu.
  • TABELA – nazwa tabeli bazy SQL do której mają zostać dopisane dane.
  • ZAKŁADKA – dotyczy importu z pliku xls.
  • KOLEJNY SCHEMAT – podanie numeru referencyjnego schematu jaki ma być wykonany automatycznie jako kolejny. Zastosowanie gdy w 1 pliku zapisane są dane które mają być wgrane do różnych tabel powiązanych, np. dokument zlecenia i kartoteki klientów.
  • REFNO – numer referencyjny schematu nadawany automatycznie w momencie tworzenia nowego schematu – dopisywania rekordu w tabeli.
  • ŹRÓDŁO DANYCH – domyślnie puste, powoduje zapis danych w tabeli w bazie na podstawie definicji connection string softwarestudioConnectionString, można podać inną nazwę połączenia do bazy aby dokonać zapisu w innej bazie np. customConnectionString.
  • DEFINICJA UPDATE – standardowo import wykonuje polecenie dopisywania danych do bazy (INSERT) możemy jednak zmienić tryb na aktualizację danych wypełniając kolumnę poleceniem UPDATE= i podając nazwę kolumny stanowiącej klucz aktualizacji danych np. UPDATE=INDEKS oznacza, że dane będą aktualizowane wg unikalnej kolumny INDEKS.
  • SYSTEMOWE – oznaczenie czy dany zapis stanowi konfigurację systemową i będzie kopiowany z bazy root podczas instalacji
  • ROLASYS – możliwość oznaczenia schematu wg symbolu roli systemowej, tak aby wyświetlając transakcję importu (import_xls.aspx) móc pozwolić użytkownikowi wybrać pozycje tylko dla roli w której pracuje użytkownik. W przypadku polecenie UPDATE warunek jest dodawany do klauzuli WHERE czyli aktualizowane będą rekordy dla wskazanego symbolu roli systemowej.
  • MAGAZYN – możliwość ograniczenia wyboru schematów importu do pozycji przypisanych do danego magazynu, w przypadku polecenie UPDATE warunek jest dodawany do klauzuli WHERE czyli aktualizowane będą rekordy dla wskazanego symbolu magazynu.
  • PRX – możliwość ograniczenia wyboru schematów importu do pozycji przypisanych do oznaczenia PRX.
  • FILTR – możliwość wprowadzenia dowolnego dodatkowego warunku filtrowania wykorzystywanego przy aktualizacji danych (UPDATE). Warunek pozwala wykluczyć wykonanie update dla pozycji, która nie spełnia dodatkowego warunku. Warunek może zawierać odwołania do zmiennych systemowych np. @MAGAZYN, @ODDZIAL, @MPK, @FIRMA, @KTO
  • TYP_PLIK – Dla rekordu nagłówkowego (typdanych=H), określa w jakim formacie będzie importowany plik, domyślnie gdy jest pusta wartość w kolumnie lub NULL jest to plik w formacie *.xls, ale można wpisac *.dat, *.txt, *.csv do importu plików tekstowych
  • SEPARATOR – dla definicji importu z plików typu CSV należy podać znaki znak będzie znacznikiem podziału poszczególnych kolumn, należy podać znak przecinek (,) średnik (;) lub pionowa kreska – pipe (|).
• Pozycji – okno otwiera się po zaznaczeniu w tabeli nagłówka schematu i wybraniu polecenia „Edytuj kolumny”
  • AKTYWNE – możliwość wyłączenia z importu określonej kolumny
  • KOLEJNOŚĆ – pozwala określić w jakiej kolejności kolumny będą przetwarzane i zapisywane w historii importu.
  • OPIS KOLUMNY – informacja co zawierać powinna kolumna
  • KOLUMNA SQL – nazwa kolumny w tabeli do której mają zostać wpisane dane
  • WARTOŚĆ – informacja jaka ma zostać wpisana do do bazy SQL
    • Wartość odczytana z pola pliku csv zgodnie z określoną kolejnością.
    • Wartość stała wpisywana do kolumny tabeli SQL poprzedzona musi być symbolem #, np. ACH ma mieć przypisaną wartość 1, to wprowadzamy #1
    • Wartość zmienna odczytywana z kontekstu zalogowanego użytkownika poprzedzona musi być symbolem @, Odczytywany może być każdy parametr przypisany do zalogowanego użytkownika  (kolumny z tabeli _users) oraz specjalne parametry np.
      • @ROLA – kod roli do której zalogowany jest użytkownik, odczyt z tabeli _users
      • @ROLASYS – kod roli systemowej do której zalogowany jest użytkownik, odczyt z tabeli _users
      • @ODDDZIAL – symbol oddziału do którego zalogowany jest użytkownik, odczyt z tabeli _users
      • @MAGAZYN – kod magazynu w jakim pracuje użytkownik, odczyt z tabeli _users
    • Wartość uzyskiwana w wyniku działania funkcji poprzedzona znakiem $
      • $REFNO – generuje nowy unikalny numer referencyjny rekordu
      • $DOKUMENT_REFNO – pobiera numer referencyjny importu danych, wykorzystywany gdy chcemy całość importu oznaczyć wspólnym identyfikatorem, np. pozycje dokumentu zlecenia magazynowego mają być zgrupowane w jeden dokument.
      • $LICZNIK – nadaje kolejny numer dokumentu wykorzystując funkcje numeracji. Wartość wymaga wskazania parametrów pozwalających określić metodę i format zwracanego numeru, np. $LICZNIK,AAA,BBB,#RR#XXXX (http://www.programmagazyn.pl/numeracja-dokumentow-funkcja-licznik/) :
        • AAA – 3 znakowy identyfikator numeracyjny
        • BBB – 3 znakowy symbol dodawany na początku numeru
        • #RR#XXXX – format zwracanych danych funkcji licznik, gdzie RR oznacza symbol roku, a XXXX formatowanie kolejnego numeru.
      • $NRIDODN – odczytuje identyfikator kontrahenta wybranego z listy podczas importu – lista rekordów z tabeli KNKON wyświetla gdy transakcja jest uruchamiana z parametrem nridodn
      • $TEST_WYSTEPOWANIA – funkcja pozwala odczytać dowolną informację z dowolnej tabeli na podstawie wybranej kolumny. Przekazujemy 4 parametry: po przecinku za nazwą funkcji, np. odczyt identyfikatora kontrahenta na podstawie nazwy skróconej $TEST_WYSTEPOWANIA,NRIDODN,KNKON,SKROCO,NAZWA_KLIENTA
        • NRIDODN – to nazwa kolumny, której wartość chcemy odczytać, np. identyfikator kartoteki klienta
        • KNKON – nazwa tabeli z której dane będą pobrane, np. kartoteka klientów
        • SKROCO – nazwa kolumny w tabeli wg której będzie wykonywane poszukiwanie rekordu, np. nazwa skrócona
        • NAZWA_KLIENTA – nazwa kolumny w pliku w której przechowywana jest informacja do wyszukania np. nazwa skrócona. W parametrze mogą zostać zapisane zmienne sesyjne oraz identyfikator kontrahenta (NRIDODN zapisane w parametrach transakcji lub NRIDODN kontrahenta wybranego z combo). Parametry należy zapisać ze znakiem @, np @NRIDODN.
      • $TEST_WYSTEPOWANIA – druga wersja funkcji pozwala odczytać dowolną informację z dowolnej tabeli na podstawie wybranej kolumny. Przekazujemy 5 parametrów: po przecinku za nazwą funkcji, np. odczyt identyfikatora kontrahenta na podstawie nazwy skróconej $TEST_WYSTEPOWANIA,NRIDODN,KNKON,SKROCO,NAZWA_KLIENTA,FILTR
        • NRIDODN – to nazwa kolumny, której wartość chcemy odczytać, np. identyfikator kartoteki klienta
        • KNKON – nazwa tabeli z której dane będą pobrane, np. kartoteka klientów
        • SKROCO – nazwa kolumny w tabeli wg której będzie wykonywane poszukiwanie rekordu, np. nazwa skrócona
        • NAZWA_KLIENTA – nazwa kolumny w SQL, w której została zapisana informacja do wyszukania np. nazwa skrócona
        • FILTR – przekujemy dodatkowy warunek filtrowania w celu sprecyzowania pozycji, której szukamy. Możemy podać dowolny poprawny warunek filtrowania i wykorzystać zmienne: @NRIDODN, @ROLASYS, @MAGAZYN, @ODDZIAL
      • $ID_IMPORT – numer referencyjny importu
      • $ID_XLS – numer referencyjny wzorca importu refno z tabeli x_xls
      • $SUBSTRING – funkcja pozwalająca wykonać polecenie substring na danych pobranych z pliku. Wymagane jest podanie 4 parametrów: np. $SUBSTRING,INDEKS,3,10
        gdzie:
        • pierwszy to nazwa funkcji $SUBSTRING
        • drugi parametr to nazwa kolumny z pliku skąd mają być pobrane dane
        • trzeci parametr wskazuje od którego znaku mają być dane czytane
        • czwarty parametr wskazuje ile znaków ma być odczytanych, jeżeli podamy wartość 0 to będą odczytane wszystkie dane do końca ciągu znaków. Powyższy przykład wywołania oznacza pobranie danych z kolumny INDEKS od 3 znaku i maksymalnie kolejnych 10 znaków.
      • $DPCEC – funkcja pozwala zapisywać dane z kolumn tabeli excela bezpośrednio do tabeli dpcec jako cechy dla danego wiersza. Przykładowo importujemy kartoteki do których w jednej tabeli excela zapisane są cechy. Cechy te chcemy zaimportować bezpośrednio do tabeli dpcec, wykorzystujemy do tego funkcje $DPCEC. Więcej na ten temat: http://www.programmagazyn.pl/studio-system/konfiguracja/ustawienia/import-z-excela-plik-xls/funkcja-dpcec/
      • $NUMER_REFNO,NAZWA_KOLUMNY_SQL – funkcja pozwala na nadanie wspólnego numeru referencyjnego według danej kolumny, podajemy nazwę kolumny z SQL nie z pliku csv. Przykładem może być nadanie wspólnego numer dokumentu dla wielu pozycji dokumentu podczas importu wielu dokumentów.
  • TYP DANYCH – oznaczenie w jaki sposób dane wgrywane do bazy SQL mają być formatowane
    • C – format tekstowy, także dla dat podanych w sposób jawny np 2017-05-13 lub 2017-05-14 00:00:00
    • N – format numeryczny dla wartości całkowitych (int)
    • M – (-1) * format numeryczny dla wartości całkowitych (int). Wartość numeryczna z kolumny Excela jest mnożona przez (-1) – w ten sposób otrzymujemy wartości liczbowe ujemne. Typ danych wykorzystywany przy imporcie np. zleceń ZWZ.
    • F – format numeryczny dla wartości zmiennej (float)
    • D – format daty oznaczający ilość dni zaczynając od 1899-12-30. Jeżeli w pliku CSV jest podana data w sposób jawny (np. 2017-05-16 13:30:00) należy wybrać typ C
    • T – format datetime. Dane w formacie yyyy-MM-rr HH:mm lub yyyy-MM-rr HH:mm:ss
    • B – format typu logicznego True/False. Program weryfikuję poprawność wprowadzonych danych, jeżeli dane w tabeli excel są niepoprawne to zostanie zapisana wartość false, Dopuszczalne wartości do zapisu jako True to: T, t, TAK, tak, 1, pozostałe zostaną zinterpretowane jako false.
  • WYMAGANE – oznaczenie na TAK oznacza że rekord zostanie skopiowany do bazy danych pod warunkiem że dana kolumna pliku zawiera informacje, jeżeli jest pusta (brak danych) to rekord nie będzie do bazy SQL wgrany. Oznaczenie pozwala ograniczyć błędne wpisy z wierszy o brakujących danych wymaganych przez bazę SQL.

 Import danych

Import wykonuje się za pomocą transakcji role_sys/import_xls.aspx

Transakcja pozwala na wybranie pliku do importu, wybór schematu jaki ma być użyty do wgrywania danych i opcjonalnie kontrahenta.

Parametry uruchamiania transakcji

Uruchamiając transakcję role_sys/import_xls.aspx można określić pewne zachowania

Wybór kontrahenta

Pozycja wyboru kontrahenta pojawia się wyłącznie, gdy uruchamiając transakcję import_xls.aspx wywołamy z parametrem nridodn=

np. role_sys/import_xls.aspx?nridodn=

Jeżeli parametr ma wartość pustą to wyświetlona lista pozwali na wybór kontrahenta, jeżeli jednak podany identyfikator kontrahenta (nridodn) jako wartość, to zostanie ona automatycznie ustawiona bez możliwości jej zmiany. Zastosowanie – gdy chcemy importować dane uruchamiając transakcję w kartotece kontrahenta.

np. role_sys/import_xls.aspx?nridodn=1231546

Wybór schematu importu

Możemy wskazać jaki schemat ma być automatycznie podstawiony, tak aby użytkownik tylko wskazał plik i mógł uruchomić import danych. W tym celu należy przekazać parametr refno i podać numer identyfikujący dany schemat importu, np. role_sys/import_xls.aspx?refno=123465790

Wskazanie stałej ścieżki importu

Parametr path umożliwia wskazanie skąd (z jakiej lokalizacji serwera) ma nastąpić import pliku. Parametr wymaga nie tylko podania ścieżki dostępu do pliku ale także wskazania jego nazwy. Po wskazaniu ścieżki i nazwy importowanego pliku wywołanie transakcji następuje bezobsługowo, tzn. po wywołaniu transakcji plik zostaje automatycznie zaimportowany. W przypadku jeżeli import wymaga wskazania wartości NRIDODN musi zostać on zaszyty w parametrach transakcji lub import winien zostać wywołany z poziomu kartoteki kontrahentów z wymuszeniem wcześniejszego wskazania kartoteki kontrahenta, którego import dotyczy.

Przykład wywołania transakcji z parametrem path (wywołany z poziomu rejestru kartotek klientów):

/role_sys/import_xls.aspx?refno=3336161320190&raport=1&path=c:\iis_demo\test_xls\Amadeus.xls&nridodn=

Po wykonaniu transakcji wyświetlany jest raport informujący o zaimportowanych rekordach do bazy

Przykład aktualizacji danych UPDATE

Chcemy z pliku XLS zaktualizować informacje o stanach dostępnych oraz cenach zakupu jakie przekazuje nad dostawca. Dane chcemy wgrać do kartotek asortymentowych (tabela KNASO) dla magazynu MG, rola DAN, gdzie identyfikatorem każdej kartoteki jest indeks towarowy. W kolumnie Definicja UPDATE wpisujemy polecenie UPDATE=INDEKS, w kolumnie rola DAN, w kolumnie Magazyn MG

Następnie definiujemy kolumny danych które będą pobierane z arkusza excel

• INDEKS, do identyfikacji kartoteki, oznaczamy jako wymagana, kolumna w bazie INDKES, kolumna w pliku xls to Indeks
• Cena do aktualizacji ceny zakupu, kolumna w bazie CENZAK, kolumna w pliku xls to Cena
• Stan do aktualizacji ilości dostępnej, kolumna w bazie ILOSC kolumna w pliku xls to Stan

Konfiguracja gotowa, można uruchamiać polecenie importu.

Przykład importu zleceń wydania z pliku CSV

Chcemy zaimportować zlecenie wydania z pliku z innego systemu ERP np. klienta magazynu. W pliku mamy kilka kolumn rozdzielonych znakiem średnik.

6365906317306693;1;130010;ZOTT MONTE DRINK 200ML 12SZT;2018-05-19 00:00:00;4014500041780;2,830;-12,000;SZT
6365906317306693;2;130009;AGROS ŁOWICZ SOS SPAGHETTI 500G 6SZT;2019-06-30 00:00:00;5900397016255;3,600;-6,000;SZT
6365906317306693;3;130008;SCHWEPPES ORANGINA CZERWONA POMARAŃCZA 1,4L 6SZT;2018-07-06 00:00:00;8435185953513;8,750;-6,000;SZT

gdzie:

• Numer identyfikacyjny klienta magazynu (NRIDODN)
• Numer kolejny pozycji, importujemy np. do kolumny ILOSC0
• Indeks towaru – na tej podstawie odczytamy wewnętrzny identyfikator asortymentu (NRIDASN)
• Opis pozycji (UWAGIPOZ)
• Oczekiwana data wysyłki (DATAROZLADUNKU)
• Kod kreskowy (DPZLE_CECHA1)
• Waga jednostkowa pozycji (POBRANIE)
• Ilość do wydania (ILOSC)
• Jednostka miary (DPZLE_CECHA)

Dla takiego pliku ustawiamy szablon importu:

oraz definiujemy kolumny:

Uwaga: KOLEJNOŚĆ oznacza numer kolumny w pliku CSV, 0 oznacza dane wgrywane do systemu, a których nie ma wprost w pliku CSV.

NAZWA	TABELA_KOLUMNA	WARTOSC	TYPDANYCH	KOLEJNOSC
ROLASYS	ROLASYS	#WMS	C	0
ACH	ACH	#1	C	0
PRX	PRX	#ZWZ	C	0
TYPDOK	TYPDOK	#ZWZ	C	0
MAGAZYN	MAGAZYN	@MAGAZYN	C	0
REFNO	REFNO	$DOKUMENT_REFNO	N	0
INDEKS	NRIDASN	$TEST_WYSTEPOWANIA,NRIDASN,KNASO,INDEKS,INDEKS	N	0
NRIDODN	NRIDODN	#6365800924740996	N	0
NRDOKUMENTU	NRDOKUMENTU	$DOKUMENT_REFNO	N	0
LOGIN	LOGIN	@USERNAME	C	0
ODDZIAL	ODDZIAL	@ODDZIAL	C	0
REFNO_DPZLE	REFNO_DPZLE	$POZYCJA_REFNO	N	0
REFNO_POZYCJA	REFNO_POZYCJA	$POZYCJA_REFNO	N	0
NRIDCRM	NRIDCRM	$TEST_WYSTEPOWANIA,NRIDCRM,KNCRM,SKROCO,ODBIORCA	N	0
PRIORYTET	PRIORYTET	#01	C	0
REFNO_XLS	REFNO_XLS	$DOKUMENT_REFNO	N	0
ODBIORCA	ODBIORCA	ODBIORCA	_	1
ILOSC0	ILOSC0	ILOSC0	N	2
INDEKS	INDEKS	INDEKS	_	3
NRAWIZO	UWAGIPOZ	UWAGIPOZ	C	4
DATAROZLADUNKU	DATAROZLADUNKU	DATAROZLADUNKU	C	5
DPZLE_CECHA1	DPZLE_CECHA1	DPZLE_CECHA1	C	6
POBRANIE	POBRANIE	POBRANIE	F	7
ILOSC	ILOSC	ILOSC	F	8
DPZLE_CECHA	DPZLE_CECHA	DPZLE_CECHA	C	9

Automatyczny import plików ze wskazanego folderu

Istnieje możliwość pobierania plików ze wskazanego folderu i wykonania automatycznie importu wg wybranego schematu. Zastosowanie np., import zleceń przyjęć ZPZ  lub wydań ZWZ.

Backup plików

Opcjonalnie można także wykonać kopię plików oryginalnych z folderu importu do folderu typu backup, należy podać parametr autobackup a jego wartość wskazać folder docelowy. Pliki są przenoszone do wskazanego folderu backup i grupowane w podfolderach wg daty importu tj. ROK/ROKMIESIAC np. importując dane w kwietniu 2017 roku w folderze back pojawi się podfolder 2017\201704\ i do niego zostaną skopiowane pliki. Nazwa pliku zawiera datę i godzinę importu oraz nazwę oryginalną pliku.

przykład uruchomienia:

role_sys/import_xls.aspx?refno=6365982400076520&autopath=c:\app_ftp\in\&autobackup=c:\app_ftp\archiwum\

gdzie:

• refno – identyfikator szablonu importu danych
• autopath – ścieżka na serwerze IIS, gdzie znajdują się pliki do pobrania
• autobackup – ścieżka na serwerze IIS, gdzie mają być zachowane pliki po imporcie. Jeżeli nie podamy parametru to pliki po imporcie są usuwane bez zachowania kopii oryginału.