konnosDashboard
SpreadsheetImport.utils.test.ts116 lines · main
1import { describe, expect, test } from 'vitest'
2
3import {
4 inferColumnType,
5 parseSpreadsheetText,
6} from '@/components/interfaces/TableGridEditor/SidePanelEditor/SpreadsheetImport/SpreadsheetImport.utils'
7
8describe('SpreadsheetImport.utils: inferColumnType', () => {
9 test('should default column type to text if no rows to infer from', () => {
10 const mockData: Array<unknown> = []
11 const type = inferColumnType('id', mockData)
12 expect(type).toBe('text')
13 })
14 test('should default column type to text if the first row has no data to infer from', () => {
15 const mockData = [{ name: 'bob', age: '42' }]
16 const type = inferColumnType('id', mockData)
17 expect(type).toBe('text')
18 })
19 test('should default column type to text if the first row data value is null', () => {
20 const mockData = [{ id: 'null', name: 'bob', age: '42' }]
21 const type = inferColumnType('id', mockData)
22 expect(type).toBe('text')
23 })
24 test('should infer integer types correctly', () => {
25 const mockData = [{ name: 'bob', age: '42' }]
26 const type = inferColumnType('age', mockData)
27 expect(type).toBe('int8')
28 })
29 test('should infer float types correctly', () => {
30 const mockData = [{ name: 'bob', height: '161.72' }]
31 const type = inferColumnType('height', mockData)
32 expect(type).toBe('float8')
33 })
34 test('should infer boolean types correctly', () => {
35 const mockData1 = [{ name: 'bob', height: '161.72', isWorking: 'true' }]
36 const type1 = inferColumnType('isWorking', mockData1)
37 expect(type1).toBe('bool')
38
39 const mockData2 = [{ name: 'bob', height: '161.72', isRetired: 'false' }]
40 const type2 = inferColumnType('isRetired', mockData2)
41 expect(type2).toBe('bool')
42 })
43 test('should infer boolean type for a supposed boolean column if one of the rows has a null value', () => {
44 const mockData3 = [
45 { name: 'bob', height: '161.72', isRetired: 'false' },
46 { name: 'bob', height: '161.72', isRetired: 'true' },
47 { name: 'bob', height: '161.72', isRetired: null },
48 ]
49 const type3 = inferColumnType('isRetired', mockData3)
50 expect(type3).toBe('bool')
51 })
52 test('should infer objects as jsonb types correctly', () => {
53 const mockData = [{ name: 'bob', metadata: '{}' }]
54 const type = inferColumnType('metadata', mockData)
55 expect(type).toBe('jsonb')
56 })
57 test('should infer date type correctly', () => {
58 const mockData4 = [
59 { event: 'christmas', date: '2022-12-25 17:45:23 UTC' },
60 { event: 'christmas', date: '2022-12-25' },
61 { event: 'christmas', date: '2022-12-25T12:03:40Z' },
62 { event: 'christmas', date: new Date() },
63 { event: 'christmas', date: new Date().toISOString() },
64 { event: 'christmas', date: 1410715640579 },
65 { event: 'christmas', date: '25 Dec 2022' },
66 { event: 'christmas', date: 'Dec 25 2022' },
67 ]
68 const type4 = inferColumnType('date', mockData4)
69 expect(type4).toBe('timestamptz')
70 })
71})
72
73interface SampleRow {
74 name: string
75 age: string | null
76 city?: string | null
77}
78
79describe('SpreadsheetImport.utils: parseSpreadsheetText', () => {
80 test('should keep empty cells as empty strings if no headers given', async () => {
81 const csv = `name,age\nJohn,25\nJane,`
82 const { rows } = await parseSpreadsheetText({ text: csv, emptyStringAsNullHeaders: [] })
83 expect((rows[1] as SampleRow).age).toBe('')
84 })
85
86 test('should convert empty cells to null when treatEmptyAsNull is true', async () => {
87 const csv = `name,age\nJohn,25\nJane,`
88 const { rows } = await parseSpreadsheetText({ text: csv, emptyStringAsNullHeaders: undefined })
89 expect((rows[1] as SampleRow).age).toBeNull()
90 })
91
92 test('should not affect non-empty values when treatEmptyAsNull is true', async () => {
93 const csv = `name,age\nJohn,25\nJane,`
94 const { rows } = await parseSpreadsheetText({ text: csv, emptyStringAsNullHeaders: undefined })
95 expect((rows[0] as SampleRow).name).toBe('John')
96 expect((rows[0] as SampleRow).age).toBe('25')
97 })
98
99 test('should handle multiple empty cells across columns when treatEmptyAsNull is true', async () => {
100 const csv = `name,age,city\nJohn,,\nJane,30,`
101 const { rows } = await parseSpreadsheetText({ text: csv, emptyStringAsNullHeaders: undefined })
102 expect((rows[0] as SampleRow).age).toBeNull()
103 expect((rows[0] as SampleRow).city).toBeNull()
104 expect((rows[1] as SampleRow).age).toBe('30')
105 expect((rows[1] as SampleRow).city).toBeNull()
106 })
107
108 test('should return correct headers regardless of treatEmptyAsNull', async () => {
109 const csv = `name,age\nJohn,25`
110 const { headers } = await parseSpreadsheetText({
111 text: csv,
112 emptyStringAsNullHeaders: undefined,
113 })
114 expect(headers).toEqual(['name', 'age'])
115 })
116})